You are a database developer on an instance of SQL Server 2008. The development team has recently developed and compiled an assembly using a .NET language. You register the assembly. You want to be able to reference the appropriate class methods from Transact-SQL. Which action should you take?
A. Create a CLR stored procedure including the WITH RECOMPILE option.
B. Create a CLR stored procedure including the EXTERNAL NAME clause.
C. Create a CLR user-defined function to call the method.
D. No additional action should be taken other than referencing the methods in the assembly from Transact-SQL.
Correct Answer: B
Explanation/Reference:
In SQL Server 2005 and above, you can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types.
Creating a CLR stored procedure in SQL Server involves the following steps:
1. Define the stored procedure as a static method of a class in a language supported by the .NET Framework. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.
Example:
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
[SqlProcedure]
public static void ExecuteToClient()
{ using (var con = new SqlConnection("context connection=true"))
{ con.Open();
var command = new SqlCommand("select @@version", con);
//To send the results of a query directly to the client, use one of the overloads of the Execute method on the SqlPipe object.
//This is the most efficient way to return results to the client, since the data is transferred to the network buffers without
//being copied into managed memory.
SqlContext.Pipe.ExecuteAndSend(command);
}
}
/// <summary>
/// Execute a command and send the resulting reader to the client
/// </summary>
[SqlProcedure] public static void SendReaderToClient()
{ using (var con = new SqlConnection("context connection=true"))
{ con.Open();
var command = new SqlCommand("select @@version", con);
//This method is slightly slower than the direct method described previously, but it offers greater flexibility to manipulate
//the data before it is sent to the client.
SqlContext.Pipe.Send(command.ExecuteReader());
}
}
[SqlProcedure]
public static void PriceSum(out SqlInt32 value)
{ using (var con = new SqlConnection("context connection=true"))
{ value = 0;
var command = new SqlCommand("SELECT Price FROM Products", con);
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{ while (reader.Read())
{ value += reader.GetSqlInt32(0);
}
}
}
}
} csc /t:library /out:MySqlServerProject.dll StoredProcedures.cs
2. Register the assembly in SQL Server by using the CREATE ASSEMBLY statement.
Example:
CREATE ASSEMBLY MySqlServerProject FROM ‘C:ProgrammingMySqlServerProject.dll’
3. Create the stored procedure that references the registered assembly by using the CREATE PROCEDURE statement. The EXTERNAL NAME clause must be specified in the CREATE PROCEDURE statement to reference the appropriate method.
Example:
CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME MySqlServerProject.StoredProcedures.PriceSum
You should not create a CLR stored procedure including the WITH RECOMPILE option. The WITH RECOMPILE clause is used with the CREATE PROCEDURE statement to specify that a stored procedure should be recompiled each time it is called.
You should not create a CLR user-defined function to call the method. There is no need to create a CLR user-defined function to make a CLR stored procedure available. After registering the assembly, you can simply create the procedure.
The option that states no additional action should be taken other than referencing the methods in the assembly from Transact-SQL is incorrect. You must create a stored procedure specifying an EXTERNAL NAME clause that names the method(s) that may be referenced.
