To be clear, one version of the CLR usually has several versions of the .NET Framework that it works with. However, one version of the .NET Framework only works with one specific version of the CLR. For example, CLR version 2.0 works with versions of the .NET Framework 2.0, 3.0, and 3.5, while CLR 4.0 works with all versions of version 4.x.NET Framework (that is, 4.0, 4.5 [.x], 4.6 [. x], 4.7 [.x], etc.). To view the relationship diagram of the CLR version to the Framework structure, see the MSDN Page for . Versions and dependencies of the .NET Framework .
As for the SQLCLR code, SQL Server only works with one version of the CLR, and the specific version depends on the version of SQL Server. SQL Server 2005, 2008, and 2008 R2 only work with CLR version 2. Because CLR version 2 only works with .NET Framework versions 2.0, 3.0, and 3.5, this means that SQL Server 2005, 2008, and 2008 R2 only work with. NET Framework versions 2.0, 3.0 and 3.5.
Of course, the SQL Server 2005 CLR integration function (source version) was built on the .NET Framework version 2.0 (since that was what was available at that time), so there are several new libraries in .NET. Framework versions 3.0 and 3.5 that do not work in SQL Server 2005 without importing them manually (i.e. System.Core and System.Xml.Linq ). In the same rows, SQL Server 2012, 2014, 2016, and 2017 are statically linked to CLR version 4, which works with the .NET Framework versions 4.0, 4.5 [.x], 4.6 [.x], and 4.7 [.x].
As for the information returned from both System.Environment.Version (in .NET code) and SELECT [version] FROM sys.dm_clr_properties; , they report the CLR version, not the .NET Framework > version. Therefore, be careful not to confuse the two things that communicate 2.0 or 4.0, since you can only use Framework version 2.0 or 4.0.
And, fortunately, due to backward compatibility, code compiled with CLR 2 Framework versions (2.0, 3.0, and 3.5) will run without having to recompile in SQL Server 2012 and later, even if they are on CLR version 4.
This way, as a rule, you will not be mistaken in using the target version 2.0, but you can certainly use versions of the Framework outside of 2.0.
For a deeper understanding of SQLCLR code, check out the following article (and the series as a whole) that I wrote:
Ladder to SQLCLR Level 5: Development (using .NET in SQL Server)