How to Change SQL Server Compatibility level

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,187 total views,  1 views today