Saturday, October 29, 2016

How do you change the compatibility level of a SQL Server database?

The compatibility level of a SQL Server database matters since the T-SQL code from a higher compatibility level will not work in a database with lower compatibility level.
How do you change the compatibility level?

Launch SQL Server Management Studio and pick the database for which you want to find the compatibility level.

Right click the database node. From the drop-down pick Properties. In the properties page click Options and you will find the compatibitlity level as shown.


The TestPubs database (a renamed Microsoft legacy sample, pubs database) has a compatibility level of 110 (SQL Server 2012). This database was brought over to SQL Server 2016 by a process which did not change the comaptibility level.

Click on the Compatibility level and from the drop-down choose the one you want. For example to be cmpatible with SQL Server 2016 you would choose 130.


Of course you can always go back to the previous level any time.

No comments: