SQLCLR for the .NET Developer
Learn about SQLCLR, writing database objects in managed code, and configuring SQL Server to use code located within .NET assemblies.
by Jason Follas
May 1, 2006
The latest version of Microsoft's flagship database, SQL Server 2005, introduces an enormous number of enhancements to the product's security, scalability, performance, and programmability features. One such major enhancement (to the delight of .NET developers everywhere, who often struggle with writing complex Transact-SQL) is SQLCLR, and with it, the promise of being able to write .NET code that will run on the database itself.
SQLCLR is a specialized version of the .NET Common Language Runtime (CLR) that is hosted within the SQL Server 2005 database engine. Unlike the CLR provided by the "regular" .NET Framework that is managed by the operating system, SQLCLR is managed entirely by the database engine, and thus allows SQL Server to continue to do the things it does well, such as manage its own memory and processes and provide security. SQLCLR is also commonly referred to as "CLR Integration."
However, just because you can run complex business logic on the data tier does not mean you always should. The database is an integral and often critical part of any architecture, and you should take precautions to ensure that the server's valuable resources are not misused by tasks that could easily be offset to a middle tier.
SQLCLR becomes valuable, though, in cases where a large amount of "throw-away" data would normally be transferred across the network for processing by business logic (because you eliminate the bottleneck caused by the network), or where you need to use functionality that is already provided by the .NET Framework but would be difficult or impossible to re-create using Transact-SQL alone.
Comparison to Transact-SQL
SQL Server's native language, Transact-SQL (T-SQL), is an interpreted language that in itself never actually compiles into native code. It is optimized for set-based data operations (for example, one action that is applied to multiple rows of data at one time instead of individually), and has direct access to the data store through the Query Processor.
Now, a lot of database folks might argue that T-SQL does compile when you save it or before a batch of dynamic SQL executes. There are some important compile-like actions that take place during this time, such as syntax checking, resolving references to database objects, and creating query execution plans. But my point is that T-SQL itself remains to be interpreted at run time, which becomes particularly pertinent when comparing T-SQL expressions that do not perform data access to their SQLCLR counterparts.
Despite (or perhaps because of) all its optimizations, T-SQL is not an all-purpose programming language. For example, T-SQL does not support object-oriented programming concepts, such as interfaces, classes, or inheritance, nor does it support language constructs that are normally taken for granted, including arrays, collections, for-each loops, and bit-shifting (you do take bit-shifting for granted, don't you?).
On the other hand, code that is executed by the CLR (managed code) is first compiled into Microsoft Intermediate Language (MSIL). At run time, segments of MSIL are Just-In-Time (JIT) compiled into native machine code just prior to execution. The resulting native code is cached for a period of time, so subsequent calls to the same routine will not need to be JIT compiled.
Managed code is created using modern-day languages, such as Visual Basic .NET and C#, and these languages support all the modern-day programming constructs. Also, an incredible amount of functionality is already provided by the .NET Framework's Base Class Library (BCL), and can readily be used by managed code.
This might sound like SQLCLR is a prime candidate to fully replace T-SQL, but here's the catch: SQLCLR does not provide direct access to the data store. So, in order to perform data operations, managed code must make use of ADO.NET to execute T-SQL. This is slower than using T-SQL directly, but faster than running the same code on another machine because the data does not need to be transferred across a network.
Back to top