A friend of mine was asking about the affect of multi-targeting in Visual Studio 2008 on SQLCLR. For an explanation of how multi-targeting works, reference David Kean's blog entry on Visual Studio 2008 multi-targeting and FXCop.
I happened to have an instance of SQL Server 2005 RTM (which uses .NET framework version 2.0.50727.42) on hand to try. I took two assemblies as a test. Just for fun, one assembly contained a user-defined function that works on SQL Server 2005, but not on SQL Server 2008 (reference my blog posting on 'Semantic (possibly breaking) change in SQLCLR TVFs') but contains NO new 3.5-specific functionality. It simply reads the event log and returns a table in a SQLCLR TVF. One assembly used System.DateTimeOffset (a type in 2.0.50727.3053 but not in 2.0.50727.42) internally (ie, not as an input or output parameter, but inside of a method I intended to try as a SQLCLR UDF).
I compiled both assemblies on a system with Visual Studio 2008 (.NET 2.0.50727.3053) targeting the .NET Framework 3.5. As an aside, when I tried targeting .NET 2.0, running code analysis gave a warning about DateTimeOffset. Move both 3.5-targeted assemblies to my SQL Server 2005 RTM system.
The assembly with no 3.5-specific functionality cataloged and ran fine in SQL Server 2005 RTM. Attempting to catalog the assembly that included DateTimeOffset failed CREATE ASSEMBLY with the error message:
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'UseDateTimeOffset' failed because assembly 'UseDateTimeOffset' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message [ : UserDefinedFunctions::GetTimeSpan][mdToken=0x6000001] Type load failed.
where UseDateTimeOffset.dll is my assembly and UserDefinedFunctions.GetTimeSpan is the public static method that would have used it.
Hope that clarifies things. You can use 3.5-targeted assemblies on SQL Server 2005 as long as they don't use functionality (the type System.DateTimeOffset is in mscorlib.dll) that don't exist in the SQL Server 2005 machines's version of .NET.
BTW, you CAN use any of the new .NET functionality in SQL Server 2005 as long as you install .NET 3.5 on the SQL Server machine. Obviously that doesn't mean you can use DateTimeOffset as a stored procedure/UDF parameter, but you can use it inside a stored procedure called by SQLCLR. Just like you can use Array in your implementation, but not expose it to T-SQL. I mentioned one cavaet to installing .NET 3.5 on a SQL Server 2005 machine in a previous blog posting earlier this year. Also, SQL Server 2005 SP2 won't recognize System.Core.dll as a "safe" assembly (as it is in SQL Server 2008) but that's to be expected.