Since SQL Server 2005 we have had the ability to write User Defined Types (UDT's) and User Defined Aggregates (UDA's) etc. with the C# programming language that are executed by the .net framework CLR that is loaded within the SQL Server process.
In SQL Server 2008 the latest service release of the .net framework CLR version 2.0 is loaded within the SQL Server process. Given that the .net framework 4.0 introduces a new version of the CLR, which CLR be loaded by SQL Server if both the 2.0 and 4.0 CLR's are installed?
SQL Server 2008 and the forthcoming SQL Server 2008 R2 release, previously codenamed "Kilimanjaro", will both continue to load the latest service release of the version 2.0 CLR.
You might be wondering, what is the rationale behind SQL Server continuing to load version 2.0 of the CLR? 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 now 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, 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 future versions of SQL Server may load newer versions of the CLR, or even support the loading of multiple CLR's within the process, version 2.0 of the CLR is here to stay for SQLCLR within SQL Server 2008 and SQL Server 2008 R2.
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.
Within the SQL Server 2008 Management Studio you can determine the current 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 2008 to determine information regarding the application domains, loaded assemblies, and tasks. The results of executing these queries can be seen in 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
For more complete information about compiler optimizations, see our Optimization Notice.