SQL Server 2012 - SQLCLR .NET Framework Version

Since SQL Server 2005 we have had the ability to write User Defined Types (UDT's) and User Defined Aggregates (UDA's) etc. that are then executed by the .net framework within the SQL Server process. SQL Server 2012 now uses version 4.0 of the CLR although previous versions of SQL Server (from SQL Server 2005) used version 2.0 of the CLR.

If you’re interested in SQLCLR on SQL Server 2008 R2 and earlier please see my previous blog post SQL Server 2008 R2 – SQLCLR .NET Framework Version, otherwise if you’re interested in SQL Server 2012 read on.

Assuming you have configured the SQL Server 2012 instance to enable SQLCLR you can determine the CLR version by executing the following query against the sys.dm_clr_properties view:

select * from sys.dm_clr_properties

Views are also available within SQL Server 2012 to determine information regarding the application domains, loaded assemblies, and tasks. The results of executing these queries can be seen in the Microsoft SQL Server Management Studio below.

select * from sys.dm_clr_appdomains
select * from sys.dm_clr_loaded_assemblies
select * from sys.dm_clr_tasks

If you need to configure the SQL Server 2012 instance to enable SQLCLR simply execute the following:

sp_configure ‘clr enabled’, 1
go
reconfigure
go

You might be wondering, what was the rationale behind SQL Server continuing to load version 2.0 of the CLR until SQL Server 2012? Is it merely a healthy cautious attitude so existing UDT's, UDA's, etc. are not broken; or is it because additional engineering would be required to support the new CLR?

It seems the answer is actually a little of both.

With the .net framework 4.0 we have the ability to load two or more distinct versions of the CLR within a single process. In previous releases of the .net framework, a process could only load a single instance of the CLR. Given this restriction the CLR team recommended that hosts, such as SQL Server 2008 R2, use the LockClrVersion function to determine the version of the CLR to load prior to initialization. So, as stated previously, SQL Server 2008 and SQL Server 2008 R2 will continue to load the latest service release of the version 2.0 CLR as the version is locked before initialization of the CLR begins. While SQL Server 2012 loads version 4.0 of the CLR.

SQL Server 2012, and previous releases, do not support the loading of multiple CLR's within the process and so version 2.0 of the CLR is here to stay for SQLCLR within SQL Server 2008 and SQL Server 2008 R2 and version 4.0 of the CLR within SQL Server 2012.

If you’re using SQLCLR today and are considering upgrading to SQL Server 2012 there are some breaking changes although they are fairly minimal, see Breaking Changes to Database Engine Features in SQL Server 2012.

Book Recommendations

If you're looking for more information on programming within SQL Server using the .net framework you should read Pro SQL Server 2005 Assemblies by Robbin Dewson and Julian Skinner. Other great books with content related to SQL CLR include Inside Microsoft SQL Server 2008: T-SQL Programming by Itzik Ben-Gan and Programming Microsoft SQL Server 2008 by Leonard Lobel, Andrew J Brust, and Stephen Forte.

Microsoft SQL Server 2012 High-Performance T-SQL Using Windows Functions and Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan  Programming Microsoft SQL Server 2012 by Leonard Lobel, Andrew J Brust and Stephen Forte can also available for preorder on Amazon.

SQL Server 2012 Management Studio – Configuring SQL CLR and associated queries