I want to create SQLCLR based stored procedures for SQL Server. As you know I can build a dll against multiple versions of .NET framework. I can't find a reference that describe which version of SQL Server supports which version of .NET framework. Can anyone help to point me the right direction?
To be clear, a single version of the CLR typically has multiple versions of the .NET Framework that it works with. A single version of the .NET Framework, however, only works with one specific version of the CLR. For example, CLR version 2.0 works with .NET Framework versions 2.0, 3.0, and 3.5, while CLR version 4.0 works with all of the 4.x versions of the .NET Framework (i.e. 4.0, 4.5[.x], 4.6[.x], 4.7[.x], etc). To see the chart of CLR version to Framework version relationships, see the MSDN page for .NET Framework Versions and Dependencies.
With regards to SQLCLR code, SQL Server only works with a single version of the CLR, and the specific version depends upon the version of SQL Server. SQL Server 2005, 2008, and 2008 R2 work only with CLR version 2. Since 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, SQL Server 2005's CLR Integration feature (the initial version of it) was built around .NET Framework version 2.0 (as that is what was available at the time), so there are a couple of newer libraries in .NET Framework versions 3.0 and 3.5 that don't work in SQL Server 2005 without manually importing them (i.e. System.Core and System.Xml.Linq). Along those same lines, SQL Server 2012, 2014, 2016, 2017, 2019, and 2022 are statically linked to CLR version 4, which works with .NET Framework versions 4.0, 4.5[.x], 4.6[.x], 4.7[.x], and 4.8.
If it makes it easier to understand, the info noted above, in chart form, is:
SQL Server version | CLR version | .NET Framework version(s)
------------------------|-----------------|----------------------------
2005 | 2.0 | 2.0, 3.0 **, and 3.5 **
| | ** To use any functionality within
| | System.Core or System.Xml.Linq
| | libraries, they must be imported
| | manually as UNSAFE.
------------------------|-----------------|----------------------------
2008 and 2008 R2 | 2.0 | 2.0, 3.0, and 3.5
------------------------|-----------------|----------------------------
2012, 2014, 2016, 2017, | 4.0 | 4.0+
2019, and 2022, (and | |
should also be Azure | |
SQL DB Managed Instance)| |
------------------------|-----------------|----------------------------
With regards to the information returned from both System.Environment.Version (in .NET code) and SELECT [value] FROM sys.dm_clr_properties WHERE [name] = N'version';, they are reporting the CLR version, not the .NET Framework version. So be careful not to confuse those two things reporting 2.0 or 4.0 as meaning you can only use Framework version 2.0 or 4.0.
And fortunately, due to backwards compatibility, code compiled against the CLR 2 Framework versions (2.0, 3.0, and 3.5) will run without needing to be recompiled in SQL Server 2012 and newer, even though they are on CLR version 4.
So, you generally cannot go wrong with using a Target Framework Version of 2.0, but you most certainly can use Framework versions beyond 2.0.
For a more in-depth look at developing SQLCLR code, check out the following article (and the series in general), which I wrote:
Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server)
For more info on working with SQLCLR in general, please visit: SQLCLR Info