SQL Server CLR: The Common Language Runtime Inside the Engine

SQL Server CLR is the Common Language Runtime, which lets you run .NET code inside SQL Server. The diagram below shows the architecture.

How the Pieces Fit

A SQL query enters through the query processor. The SQL CLR module holds the CLR objects. These are stored procedures, user-defined functions (UDFs), user-defined types (UDTs), user-defined aggregates (UDAs) and triggers. The CLR hosting module hosts the .NET Framework base class libraries (BCLs).

CLR code runs at one of three permission levels. SAFE code works inside the database, through the database engine. EXTERNAL_ACCESS code can also reach files, the registry and the network. UNSAFE code can call native DLLs and COM DLLs.

Why Use CLR

SQL Server has a performance issue with cursors and looping. When we use recursive functions or a cursor in stored procedures, we compromise on performance. With CLR, we can write the looping and recursive parts in C# or any other .NET language.

CLR stored procedures can use the built-in functionality of the classes in the .NET Framework. That makes it relatively easy to add complex mathematical expressions or data encryption. They are also compiled, while T-SQL is interpreted. That can give a significant performance advantage for code that runs many times.

When Not to Use CLR

CLR stored procedures cannot replace T-SQL statements. A normal T-SQL statement runs much faster than a CLR stored procedure. Use CLR stored procedures to avoid cursors and complex computation.

Overusing CLR is not advisable, so avoid it where plain T-SQL does the job.

Let me know your thoughts on this post. Do you use CLR stored procedures on your production server? If you do, what do you use them for?

CLR is not a replacement for T-SQL, it is a tool for the work T-SQL does poorly.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.