Posts Tagged ‘CLR Objects’

T-SQL and CLR Code in .Net 3.5 and SQL Server 2008

Due to CLR (Common Language Runtime) integration with SQL Server 2005/2008, programmers can now multiply the power of database programming using T-SQL and advanced programming languages like C# and VB.Net. We can write stored procedures, user-defined functions, triggers, aggregates and user-defined types in managed code. But we have to know what is best at what situation. T-SQL is still the best way to interact with database server and data. But what when T-SQL has to access files, environment variables or registry? Then we use extended stored procedures to perform such operations. Since extended stored procedures are not managed code, it cannot be considered a good option. But since SQLCLR objects are managed code and run within SQL Server (in-process), it can be a good option.

T-SQL code can run only at the server end, but SQLCLR code can run at both the client and server ends. And hence SQLCLR code can perform CPU intensive tasks at both the ends. This is one of the most compelling reasons to choose SQLCLR object over T-SQL code. Now a day, even client PCs are very powerful. SQLCLR objects can be used to perform complex string operations, encryption-decryption, array operations, accessing external resources like files and registry, etc. Apart from all these, we should not forget the benefits of .Net code.

Let’s take a simple example to calculate factorial of a number in both T-SQL and SQLCLR.

CREATE FUNCTION dbo.SqlFactorial(@factno decimal(38, 0))
RETURNS decimal(38, 0)
DECLARE @temp decimal(38, 0)
IF (@factno <= 1)
SELECT @temp = 1
SELECT @temp = @factno * dbo.SqlFactorial(@factno – 1)
RETURN @temp
This will create one user-defined function called dbo.SqlFactorial.

Now create SQLCLR user-defined function in .Net 3.5.
Create one project: ->Visual C# -> Database – > SQL Server Project.
A database reference will be asked for where to create the object. The wizard will guide you; it’s simple.
Right click on the project name, select Add -> User-Defined Function… Name it CLRFactorial.
Write a factorial method as:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
public static Double CLRFactorial(Double factno)
if (factno <= 1)
factno =1;
factno = factno * CLRFactorial(factno – 1);
return factno;
To test it, build the project. Modify the Test Scripts – > Test.sql file in the solution explorer of the same project to call this method: Select dbo.CLRFactorial(21);

Run the project in debug or non-debug mode to see its result in the output window.
Now it’s time to deploy this assembly to SQL Server database whose reference we added when following SQL Server Project wizard. Choose Build – > Deploy Solution. This will add CLRFactorial assembly in the database node at: -> Programmability -> Assemblies -> CLRFactorial. Then, enable CLR in SQL Server by running following command.

EXEC sp_configure ‘clr_enabled’, 1;

By this time, we have two user-defined functions to calculate factorial of a number: SqlFactorial and CLRFactorial. Test both the functions in Query Analyzer for numbers 5, 10, 25, 30, 32 and so on. Now run.

SELECT dbo.SqlFactorial(33);

A lovely message comes in result as:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

But we do not get such error message with SQLCLR function. We get result 8.68331761881189E+36 in exponential form.

See the benefit of SQLCLR code! But one should also know the PERMISSION_SET of such assembly in SQL Server before writing magical code in .Net.