MS SQL Server 2012 - DBA Articles

ALTER DATABASE - Compatibility Level of SQL Server 2012

This article describes how to view or change the compatibility level of a database in SQL Server 2012 by using SQL Server Management Studio.
    01. SQL Server 2005 default compatibility level is 90
    02. SQL Server 2008 and SQL Server 2008 R2 default compatibility level is 100
    03. SQL Server 2012 default compatibility level is 110
When a database is upgraded to SQL Server 2012 from any earlier version of SQL Server, the database retains its existing compatibility level. Compatibility level affects behaviours only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server.
Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore, the problem may be compounded if the plan is placed in the plan cache and reused for subsequent queries. To avoid inaccurate query results, we recommend the following procedure to change the compatibility level of a database:
    01. Set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.
    02. Change the compatibility level of the database.
    03. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.
More Reference URL: http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.110%29.aspx
To view or change the compatibility level of a database
Select SQL Server Name and then click Connect.

Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the Sales database and then click Properties.

In the Database Properties dialog box, click the Options page.

The current compatibility level is displayed in the Compatibility level list box.

To change the compatibility level, select a different option from the list. The choices are SQL Server 2005 (90), SQL Server 2008 (100), or SQL Server 2012 (110).

Right-click the Sales database to change, and then click Refresh.

Right-click the Sales database to change, and then click Properties.

In the Database Properties dialog box, click the Options page.

From the Restrict Access1 option, select MULTI_USER and then Click OK.

If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Database ICON changes, when updated Restrict Access = MULTI_USER Mode.

More Reference URL: http://msdn.microsoft.com/en-us/library/bb933794%28v=sql.110%29.aspx

* * * * *

Your Comment To AUTHOR