You work as the database administrator for an IT training company named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
Domain.com decides to switch to commission-based salaries for its Sales staff. You need to generate information that Rory Allen, the manager of the Sales department, will use to calculate the commission for each sales person. Your solution must meet the following criteria:
1. You must be able to use multiple SELECT statements to produce the result set.
2. The result set must be returned as a table for which the PRIMARY KEY constraint is defined.
3. Rory Allen must be able to reference the result set in the FROM clause of a SELECT statement.
What should you do?
A.
Create a stored procedure.
B.
Create a view.
C.
Create a user-defined function.
D.
Create a Common Runtime Language (CLR) function.
Explanation:
You need a user-defined function to meet the requirements for your solution.
Incorrect Answers:
A: Result sets from a stored procedure cannot be in the FROM clause of a SELECT statement.
B: A view only supports a sing SELECT statement. You need to be able to use multiple SELECT statements to produce the result set.
D: You cannot define a PRIMARY KEY constraint on the result set returned from a Common Runtime Language (CLR) function.
Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: stored procedures [SQL Server] Microsoft SQL Server 2005 Books Online (2007), Index: stored procedures [SQL Server], creating
Microsoft SQL Server 2005 Books Online (2007), Index: views [SQL Server] Microsoft SQL Server 2005 Books Online (2007), Index: functions [SQL Server],