{"id":516,"date":"2011-11-27T18:52:00","date_gmt":"2011-11-27T18:52:00","guid":{"rendered":"\/blogs\/bobb\/post\/SQLCLR-NET-4-based-behavior-differences-in-SQL-Server-2012.aspx"},"modified":"2013-01-03T23:59:29","modified_gmt":"2013-01-04T07:59:29","slug":"sqlclr-net-4-based-behavior-differences-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/","title":{"rendered":"SQLCLR .NET 4-based behavior differences in SQL Server 2012"},"content":{"rendered":"<p>\nAs I&nbsp;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&nbsp;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.\n<\/p>\n<p>\nAlthough the documentation on these differences indicate that they can be handled in the configuration file, there is no sqlservr.exe.config file, and I&#39;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&nbsp;an old blog entry <a href=\"http:\/\/3.209.169.194\/blogs\/bobb\/more-info-about-system-configuration\/\">here<\/a> for an official response&nbsp;to this.&nbsp;I&#39;ve seen nothing indicating this information is changed so far.\n<\/p>\n<p>\nInstead, how you handle these changes is difference-specific and requires no config file (or changes to machine.config; SQL Server doesn&#39;t change this either). Here&#39;s the differences and documented ways to handle them.\n<\/p>\n<p>\n1. 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.\n<\/p>\n<p>\n2. 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.\n<\/p>\n<p>\n3. .NET 4.0 introduces differences for .NET&#39;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&#39;t. Although you probably won&#39;t use .NET&#39;s internal sort ordering in SQLCLR enough to notice the difference, System.TimeSpan is the .NET type that corresponds to SQL Server&#39;s TIME data type. So watch out for format error of you use TimeSpan&nbsp;in your SQLCLR code and update the code appropriately.\n<\/p>\n<p>\nAlthough 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.\n<\/p>\n<p>\nThere&#39;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).\n<\/p>\n<p>\nNow, on to investigate .NET 4.0 features that can be used to improve SQLCLR code. I&#39;ve already noticed that .NET 4 supports ETW events, so you could combine XEvent ETW target trace&nbsp;with a .NET 4 ETW trace to determine when garbage collections are occuring, for example. More later perhaps&#8230;\n<\/p>\n<p>\n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As I&nbsp;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&nbsp;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 [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,38],"tags":[],"class_list":["post-516","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-sqlclr"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLCLR .NET 4-based behavior differences in SQL Server 2012 - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLCLR .NET 4-based behavior differences in SQL Server 2012 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"As I&nbsp;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&nbsp;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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2011-11-27T18:52:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T07:59:29+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/\",\"name\":\"SQLCLR .NET 4-based behavior differences in SQL Server 2012 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2011-11-27T18:52:00+00:00\",\"dateModified\":\"2013-01-04T07:59:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2012\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQLCLR .NET 4-based behavior differences in SQL Server 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLCLR .NET 4-based behavior differences in SQL Server 2012 - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/","og_locale":"en_US","og_type":"article","og_title":"SQLCLR .NET 4-based behavior differences in SQL Server 2012 - Bob Beauchemin","og_description":"As I&nbsp;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&nbsp;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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/","og_site_name":"Bob Beauchemin","article_published_time":"2011-11-27T18:52:00+00:00","article_modified_time":"2013-01-04T07:59:29+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/","name":"SQLCLR .NET 4-based behavior differences in SQL Server 2012 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2011-11-27T18:52:00+00:00","dateModified":"2013-01-04T07:59:29+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/sqlclr-net-4-based-behavior-differences-in-sql-server-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2012","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-server-2012\/"},{"@type":"ListItem","position":3,"name":"SQLCLR .NET 4-based behavior differences in SQL Server 2012"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=516"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/516\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}