Archive

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.

Now create SQLCLR user-defined function in .Net 3.5.
Steps:
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;

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;
RECONFIGURE;

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.