Recently I had a need to do some performance testing against a CLR assembly in SQL Server. It seemed to me that the first step to do that performance testing was to create a CLR assembly in SQL Server. It was a new experience for me, so I thought I’d share the details with you. This is how I did it.
Build a CLR assembly
The first step to running CLR code from SQL Server is to build a CLR assembly. This is just a simple .NET class library – nothing special. For the sake of the performance testing that I needed to accomplish, it didn’t need to be complicated, so neither will this version. We just need enough load to demonstrate the process. The code behind our assembly looks like this:
public static void RunPerfLoad()
{
long x = 0;
for (int n = 0; n < 100; n++)
{
x = 0;
for (int i = 0; i < 20000000; i++)
{
x += i;
}
}
}
Simple enough, right? Right.
Install the CLR assembly in SQL Server
Now that we’ve built our CLR assembly, we need to load it into our SQL Server database. Once the assembly has been built in Visual Studio, it will output a .dll. From SQL Server Management Studio, expand the database name, then expand the ‘Programmability’ folder, then right click on the ‘Assemblies’ folder, and choose ‘New Assembly…’ like so:
In the window that opens, choose the ‘Browse…’ button next to the ‘Path to assembly’:’ text box:
In the window that opens, navigate to your newly created CLR assembly and select it. After you’ve chosen the assembly, the ‘New Assembly’ window should look something like this:
Click OK on the ‘New Assembly’ window and if all is well, the new assembly will be displayed under the ‘Assemblies’ folder of your database:
How sweet is that? Pretty freaking sweet, I think!
Create a procedure that calls into the CLR assembly
Now that we’ve successfully loaded the CLR assembly into our database, we need to create a procedure that will call into the assembly. That’s fairly straightforward and can be done with the little snippet of T-SQL that’s listed below. Notice that when referencing the function, it’s specified as [AssemblyName].[NameSpaces.Type].[FunctionName]. Unfortunately, the documentation on this isn’t as straightforward as it could be. We also don’t have any input or output parameters since this is just designed to create load on the system, but if you need input/output parameters, see the last section of this post.
CREATE PROCEDURE RunPerfLoad
AS EXTERNAL NAME [SQLServerPerfAssembly].[SQLServerPerfAssembly.SQLServerPerfAssembly].[RunPerfLoad]
If this step went according to plan, you should have an awesome new procedure listed under the ‘Stored Procedures’ folder of your database. Note that the icon has a little lock on it and if you right click on the stored procedure, the ‘Modify…’ option is greyed out. This makes sense since the code is stored in an external assembly and can’t be edited directly in Management Studio.
Run the shiny new procedure
The last step is to run our new CLR function from the SQL procedure. Right clicking on the stored procedure and selecting ‘Execute Stored Procedure…’ will provide some T-SQL code that looks something like this that we can run like any other T-SQL in Management Studio:
USE [Test]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[RunPerfLoad]
SELECT 'Return Value' = @return_value
GO
In this case, our CLR assembly doesn’t really do anything productive, but if we run this T-SQL code, we do see a result that indicates the assembly ran successfully:
CLR functions with Input and Output parameters
So, what about parameters? Stored procedures are almost always more useful with input and/or output parameters. To accept parameters into your CLR function, just modify it like you would any other .NET function to accept parameters and then re-do the steps above to install the assembly:
public static void AddBook(string title, int authorID, int subjectID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = @"INSERT INTO [Test].[dbo].[Book] VALUES (@TITLE, @AUTHORID, @SUBJECTID, newid())";
command.Parameters.AddWithValue("@TITLE", title);
command.Parameters.AddWithValue("@AUTHORID", authorID);
command.Parameters.AddWithValue("@SUBJECTID", subjectID);
connection.Open();
SqlContext.Pipe.ExecuteAndSend(command);
}
}
Then, to create the related stored procedure in SQL Server, you can use:
CREATE PROCEDURE InsertBook
@Title NVARCHAR(MAX),
@AUTHORID INT,
@SUBJECTID INT
AS EXTERNAL NAME [SQLServerAssembly].[SQLServerAssembly.SQLServerAssembly].[AddBook]
Additionally, to retrieve output parameters instead, we just need to create a function that returns a value via an out parameter:
public static void GetBookTitle(int id, out string title)
{
title = null;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT Title FROM [Test].[dbo].[Book] WHERE ID=@ID";
command.Parameters.AddWithValue("@ID", id);
connection.Open();
using(SqlDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
title = reader.GetString(0);
}
}
}
}
The T-SQL to implement this function in SQL Server looks something like this:
CREATE PROCEDURE GetBookTitle
@ID INT,
@Title NVARCHAR(MAX) OUTPUT
AS EXTERNAL NAME [SQLServerAssembly].[SQLServerAssembly.SQLServerAssembly].[GetBookTitle]
And the T-SQL to run this stored procedure looks like this:
USE [Test]
GO
DECLARE @return_value int,
@Title nvarchar(max)
EXEC @return_value = [dbo].[GetBookTitle]
@ID = 3,
@Title = @Title OUTPUT
SELECT @Title as N'@Title'
SELECT 'Return Value' = @return_value
GO
If we run this stored procedure, the data is retrieved as expected:
And that’s it! You’ve successfully created a CLR assembly that runs from SQL Server. Awesome, yes?