How do I change the compatibility level in SQL?

How do I change the compatibility level in SQL?

The best practice to change the compatibility level of the database is in following three steps.

1. Set the database to single user access mode by using. ALTER DATABASE SET SINGLE_USER.
2. Change the compatibility level of the database.
3. Put the database in multiuser access mode by using.

How do I change the compatibility database in SQL Server 2005?

The best practice to change the compatibility level of database is in following three steps.

1. Set the database to single user access mode by using. ALTER DATABASE SET SINGLE_USER.
2. Change the compatibility level of the database.
3. Put the database in multiuser access mode by using. ALTER DATABASE SET MULTI_USER.

How to force the legacy CE in SQL Server?

In SQL Server 2014, you were limited to using the database compatibility level to control the CE version at the database scope. Setting the compatibility level to 110 or lower will force the legacy CE (cardinality estimation model version 70).

Is there a way to force the legacy CE?

Setting the compatibility level to 110 or lower will force the legacy CE (cardinality estimation model version 70). The problem with lowering the database compatibility level is that you can’t leverage the new engine functionality available under the latest compatibility level.

Can a database be set to a lower compatibility level?

New databases are set to this level unless the model database has a lower compatibility level. For databases attached or restored from any earlier version of SQL Server, the database retains its existing compatibility level, if it is at least minimum allowed for that instance of SQL Server.

When to use CE version 70 in SQL Server?

For a SQL Server database set at compatibility level 120 or above, activation of the trace flag 9481 forces the system to use the CE version 70.