You are a database developer on an instance of SQL Server 2008. Your Sales database contains current and historical sales information for your company.
The development team has recently created a user-defined table-valued function in .NET that accepts some input parameters and returns a list of customers ranked from highest to lowest based on their sales volume.
You have registered the assembly and issued the following statement:
CREATE FUNCTION dbo.GetRanked(@p1 int, @p2 char(5), @p varchar(20))
RETURNS TABLE (CustID int,CustName varchar(35),Volume money,Ranking int)
WITH EXECUTE AS CALLER AS EXTERNAL NAME MyAssembly.CLRFunctions.GetRankedList;
You use the result set returned by the function in many queries that include different values with the TOP keyword, and you want the queries to execute as quickly as possible.
Which action should you take?
A. Re-create the GetRanked function to include an ORDER clause.
B. Include the FAST query hint in each of your queries.
C. Re-create the GetRanked function to use EXECUTE AS SELF.
D. Create a Transact-SQL stored procedure that accepts a table-valued input parameter and inserts the values into another table, and create an index on the Ranking column of the table
Correct Answer: A
Explanation/Reference:
In this scenario, the CLR user-defined function always returns the result set in a specified order. You can optimize performance of your queries by specifying the ORDER clause for your CLR function. This will prevent the need to sort the result each time you run a query that includes the TOP keyword, and also will provide optimum performance. In this scenario, you could drop the GetRanked function and re-create it using the following statement:
CREATE FUNCTION dbo.GetRanked(@p1 int, @p2 char(5), @p varchar(20))RETURNS TABLE (CustID int,CustName varchar(35),Volume money,Ranking int) WITH EXECUTE AS CALLER ORDER(Ranking)AS EXTERNAL NAME MyAssembly.CLRFunctions.GetRankedList;
Read more: http://www.sqlskills.com/blogs/bobb/sql-server-2008-ordered-sqlclr-table-valued-functions/
You should not create a Transact-SQL stored procedure that accepts a table-valued input parameter and inserts the values into another table, and create an index on the Ranking column of the table. In this scenario, there is no need to code an additional stored procedure, nor require additional storage. Therefore, using an ordered table-valued function would be a better choice.
You should not include the FAST query hint in each of your queries. The FAST query hint is used to quickly return a certain number of rows from a query, and then return the remainder of the rows after the query completes. You would use the FAST query hint if you had a long-running query and you wanted to return the initial portion of the result while the query continued execution.
You should not re-create the GetRanked function to use EXECUTE AS SELF. The EXECUTE AS clause is used to control the security context under which the function executes. This would have no impact on query performance, but rather would determine the permissions required to use the function.