SQLCLR .NET 4-based behavior differences in SQL Server 2012

As I mentioned in a few previous blog entries, SQL Server 2012 runs the .NET 4 version inside of SQL Server. Your SQLCLR code may be affected by some behavior differences between .NET versions 2.0/3.5/3.5 SP1 and .NET 4.0. I like the way SQL Server chose to deal with these differences.

Although the documentation on these differences indicate that they can be handled in the configuration file, there is no sqlservr.exe.config file, and I'd suggest that you keep it that way, at least for now, until using/changing a config file with SQL Server is officially supported. See an old blog entry here for an official response to this. I've seen nothing indicating this information is changed so far.

Instead, how you handle these changes is difference-specific and requires no config file (or changes to machine.config; SQL Server doesn't change this either). Here's the differences and documented ways to handle them.

1. In .NET 4.0, user code does not catch Access Violation (and other corrupted state) exceptions by default. You could catch these exceptions in previous versions of .NET. To continue to catch these in SQL Server 2012, use the System.Runtime.ExceptionServices.HandleProcessCorruptedStateExceptionsAttribute attribute on the method that contains the exceptions catch block.

2. In .NET versions previous to 4.0 Code Access Security (CAS) policies are always enabled. In .NET 4.0, CAS is not enabled by default. SQL Server uses CAS as part of its permission_set implementation, and so SQL Server always enables CAS.

3. .NET 4.0 introduces differences for .NET's internal sorting (because it uses Unicode version 5.1), and .NET 4.0 throws an Invalid Format Exception on format errors in System.TimeSpan previous versions didn't. Although you probably won't use .NET's internal sort ordering in SQLCLR enough to notice the difference, System.TimeSpan is the .NET type that corresponds to SQL Server's TIME data type. So watch out for format error of you use TimeSpan in your SQLCLR code and update the code appropriately.

Although these last two (Unicode sorting and TimeSpan) can be overriden in a config file, SQL Server 2012 has a different, nicer, implementation. If the Database Compatibility Level is 110 (SQL Server 2012), the new behaviors are in effect. Setting Database Compatibility Level lower than 110 reverts to the old compatibility level.

There's example code in the .NET books online that illustrates these last two differences. I used these to verify that setting the database compatibility level works as advertised and that it works whether your TargetFramework version (your code) is .NET 3.5 or 4.0 (i.e. your TargetFramework has no effect in this behavior within SQLCLR code).

Now, on to investigate .NET 4.0 features that can be used to improve SQLCLR code. I've already noticed that .NET 4 supports ETW events, so you could combine XEvent ETW target trace with a .NET 4 ETW trace to determine when garbage collections are occuring, for example. More later perhaps…

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.