{"id":6464,"date":"2018-12-11T06:55:05","date_gmt":"2018-12-11T06:55:05","guid":{"rendered":"https:\/\/dbtut.com\/?p=6464"},"modified":"2018-12-11T06:55:05","modified_gmt":"2018-12-11T06:55:05","slug":"what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/","title":{"rendered":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level"},"content":{"rendered":"

 <\/p>\n

The Compatibility Level sets some database behaviors to work in accordance with the specified version of SQL Server. For example, for compatibility levels in SQL Server 2008 and earlier, the PIVOT command was supported in the Recursive CTE. But in SQL Server 2008 and higher compatibility levels, the PIVOT command is not supported in the Recursive CTE.<\/p>\n

Consider an application that uses a SQL Server 2008 database and has used a recursive cte in a function in the database. As the database administrator, you want to upgrade the database from SQL Server 2008 to SQL Server 2012.<\/p>\n

After the upgrade is completed; If the upgraded version supports the compatibility level of the older version, the compatibility level remains. If the compatibility level did not remain old, the application would not work because the PIVOT command was not supported in the RECURSIVE CTE.<\/p>\n

Note: If the new version does not support the compatibility level of the older version, compatibility level is automatically set to the lowest compatibility level that the new version supports. Therefore, upgrading the database to several higher versions at once can cause problems. For Example; SQL Server 2005 to 2016. In such cases, you must first upgrade to SQL Server 2012, then to SQL Server 2016.<\/p>\n

Even if the Compatibility Level is not upgraded during the SQL Server Upgrade process, I recommend that you upgrade the compatibility level to the compatibility level of the new version after the transition period. I will explain how to change the Compatibility Level in the article.<\/p>\n

 <\/p>\n

SQL Server Versions and Supported Compatibility Levels:<\/h3>\n
\n\n\n\n\n\n\n\n\n\n\n
SQL Server Version<\/td>\nCompatibility Level<\/td>\nSupported Compatibility Levels<\/td>\n<\/tr>\n
SQL Server 2000<\/td>\n80<\/td>\n80<\/td>\n<\/tr>\n
SQL Server 2005<\/td>\n90<\/td>\n90,80<\/td>\n<\/tr>\n
SQL Server 2008 and SQL Server 2008 R2<\/td>\n100<\/td>\n100,90,80<\/td>\n<\/tr>\n
SQL Server 2012<\/td>\n110<\/td>\n110,100,90<\/td>\n<\/tr>\n
SQL Server 2014<\/td>\n120<\/td>\n120,110,100<\/td>\n<\/tr>\n
SQL Server 2016<\/td>\n130<\/td>\n130,120,110,100<\/td>\n<\/tr>\n
SQL Server 2017<\/td>\n140<\/td>\n140,130,120,110,100<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

 <\/p>\n

You can see the compatibility levels of the databases with the help of the following query.<\/p>\n

select name,compatibility_level from sys.databases<\/pre>\n
 <\/p>\n
How to Change the Compatibility Level of a Database:<\/h3>\n
Right-click the database and click Properties. You can change the compatibility level from the Options tab as follows.<\/p>\n
<\/p>\n
You should use the latest SSMS version. Because you can see blank compatibility level section in older versions of SQL Server Management Studio.<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  The Compatibility Level sets some database behaviors to work in accordance with the specified version of SQL Server. For example, for compatibility levels in SQL Server 2008 and earlier, the PIVOT command was supported in the Recursive CTE. But in SQL Server 2008 and higher compatibility levels, the PIVOT command is not supported in …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6464","post","type-post","status-publish","format-standard","","category-mssql"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":1252,"today_views":1},"yoast_head":"\nWhat is SQL Server Database Compatibility Level and How To Change Database Compatibility Level - Database Tutorials<\/title>\n<meta name=\"description\" content=\"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-11T06:55:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\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\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level\",\"datePublished\":\"2018-12-11T06:55:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/\"},\"wordCount\":366,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png\",\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/\",\"name\":\"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png\",\"datePublished\":\"2018-12-11T06:55:05+00:00\",\"description\":\"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png\",\"width\":923,\"height\":226},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level - Database Tutorials","description":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level","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:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/","og_locale":"en_US","og_type":"article","og_title":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level - Database Tutorials","og_description":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level","og_url":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/","og_site_name":"Database Tutorials","article_published_time":"2018-12-11T06:55:05+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level","datePublished":"2018-12-11T06:55:05+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/"},"wordCount":366,"commentCount":2,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png","articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/","url":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/","name":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png","datePublished":"2018-12-11T06:55:05+00:00","description":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/12\/220.png","width":923,"height":226},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/11\/what-is-sql-server-database-compatibility-level-and-how-to-change-database-compatibility-level\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6464","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=6464"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6464\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=6464"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=6464"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=6464"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}