Trocando - Change Compatibility Level SQL

Possibility of impact Compatibility-level setting sql

Possibilidade de impacto alterando o Nivel de Compatibilidade do banco SQL Server

http://msdn.microsoft.com/en-us/library/bb510680.aspx

************************************************************************

SQL SERVER – 2005 – Change Compatibility Level – T-SQL Procedure

January 12, 2008 by pinaldave

Six months ago I wrote article about SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility. Yesterday I received an email asking that one of my blog reader is not able to use the sp_dbcmptlevel command with error that database is in use. He has asked me to write about proper procedure of changing database compatibility which will always work.

First read my previous article SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility as it has explained many details about compatibility.

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

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

Above three steps are translated in T-SQL in following three line script. Run this procedure in Query Editor.
ALTER DATABASE AdventureWorks
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

************************************************************************

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

May 29, 2007 by pinaldave

sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

Version of SQL Server database can be one of the following:

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005

The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

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

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

Reference : Pinal Dave (http://blog.SQLAuthority.com) , MSDN Article.

************************************************************************