How to Change SQL Server Compatibility level

How to Change SQL Server Compatibility

 

Changing Database compatibility level will be required when you upgrade your database from one version to another and you want to use the latest feature for sql version. For details on compatibility check this earlier post.

For example lets say when you upgrade a database from sql server 2012 to 2014 the compatibility level will be still on 110 .

To change it to the compatibility of SQL 2014 level of 120 below query can be used.

 

— SQL Server 2014

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 120

GO

 

For getting the compatibility level for different version please check this post.

Few notes to remember when changing the compatibility of SQL Server.

  1. You can perform testing in Development environment for checking any errors for compatibility changes or any performance issues.
  2. Once the compatibility level is change this change will immediately impact any future running queries.
  3. User can revert back the compatibility level in case if there are any errors.
  4. There is no restart of the server required when changing the compatibility level.
  5. Database compatibility will be always backward with earlier version of SQL server.

Check this post.

 4,237 total views,  3 views today

admin

Share
Published by
admin
Tags: sql server

Recent Posts

What is slow changing dimension?

The attribute value in Dimension table are going to change slowly over time and they…

5 years ago

View Compatibility level of Database

To know what is compatibility Level check this post link. You can view the compatibility…

6 years ago

SQL Server Compatibility Level

SQL Server Compatibility level will be used to decide how SQL will use it features.…

6 years ago

View in SQL server

A view is a virtual table.  View will not store any data physically. The data…

6 years ago

SSIS :The package failed to load due to error 0xC0011008

Below is the error which I was getting today while deploying the SSIS pacakge for…

6 years ago

How to enable Query store in sql server

Query store is a new powerful feature introduced in SQL 2016 which stores the query…

6 years ago