You are a database administrator on an instance of SQL Server 2008. The development team has recently created some user-defined functions in .NET that you would like to access from SQL Server.
One of the functions, named GetSample, accepts a single input parameter and returns a result set containing a random sampling of data from several tables in your current database. Users need to be able to issue queries that use the generated sample.
You successfully execute the following Transact-SQL statements:
CREATE ASSEMBLY CLRAssembly FROM ‘C:CLRTest.dll’ WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION MyFunction (@parm int)
RETURNS varchar
AS EXTERNAL NAME AssemblyName.NamespaceName.ClassName.GetSample;
When you attempt to access the function, an error is returned.
Which action should you take?
A. Re-register the assembly with the EXTERNAL_ACCESS permission set and re-create the function as an inline table-valued function.
B. Remove the EXTERNAL NAME clause from the CREATE FUNCTION statement and drop and re-create the function.
C. Drop the function and re-create it to return a table.
D. Re-create the desired functionality using a Transact-SQL stored procedure.
Correct Answer: C
Explanation/Reference:
In this scenario, the .NET UDF returns a result set, not a scalar value. When you issued the CREATE FUNCTION statement, you specified that the function returned a scalar value. You need to re-create the function with a RETURNS clause that specifies a tabular result set instead of a scalar value. You would specify the details of the columns returned by the GetSample CLR UDF.
You should not re-register the assembly with the EXTERNAL_ACCESS permission set and re-create the function as an inline table-valued function. In this scenario, there is no need for the assembly to be given the EXTERNAL_ACCESS permission set because it uses tables within your current database. The EXTERNAL_ACCESS permission set allows access to the registry, the file system, environment variables, and unmanaged code. In addition, you would not re-create the function as an inline table-valued function. An inline table-valued function would not be able to provide the details of the table returned by theGetSample function. Inline tablevalued functions are defined by including a single SELECT statement without specifically naming the columns of the result. For example, the following statement would create an inline-table valued function without specifying the details of the returned table value.
CREATE FUNCTION dbo.GetData (@parm int)RETURNS table AS RETURN (SELECT * FROM MyTableWHERE MyColumn = @parm);
You should not remove the EXTERNAL NAME clause from the CREATE FUNCTION statement and drop and re-create the function. The EXTERNAL NAME clause must be specified when creating a CLR UDF to identify the .NET function that will be referenced.
You should not re-create the desired functionality using a Transact-SQL stored procedure. In this scenario, you can access the previously created CLR function without additional development effort.
