How to change the compatibility level of a database?
You can change compatibility level of a database of a database using any of the below option:
Option 1: Using TSQL
ALTER DATABASE [Database Name] SET COMPATIBILITY_LEVEL = [Target level]
ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 100
Option 2: Using GUI
- Right click on the database and open property page.
- Select option tab from the property page of the database.
- Change the COMPATIBILITY_LEVEL from the option provided there.
However please note that changing compatibility level while users are connected to the database can produce incorrect result sets for active queries. So it is highly recommended to follow below procedure while changing the compatibility level of a database:
1. Set the database to single-user access mode or restricted access mode (if putting database in restricted access at your environment can guarantee that no user will be able to connect to database while you are working on it).
2. Change the compatibility level of the database.
3. Bring the database back to normal mode. (Multi user mode)