Compatibility in SQL Server

Compatibility Levels

COMPATIBILITY_LEVEL is a database attribute; while creating the database you can change the compatibility level, by default it is set to 100 in SQL Server 2008.

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server.

Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting.

For all installations of SQL Server 2008, the default compatibility level is 100. Databases created in SQL Server 2008 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2008 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 80. Upgrading a database with a compatibility level below 80 sets the database to compatibility level 80.

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 both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results.

Check Compatibility

There are two ways to check current compatibility Level of your database, you can use below script that will return the current compatibility Level.

First method:

sp_dbcmptlevel 'DatabaseName'

The output will be
The current compatibility level is 100.

Second Method:

Second way to check compatibility level is

use DatabaseName
select compatibility_level from sys.databases where name=db_name()

Or For all databases:

select name, compatibility_level from sys.databases
order by name

Changing the Compatibility

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 both 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, it is recommended to follow below steps to change the compatibility level of a database:

  1. Set the database to single-user access mode by using ALTER DATABASE DatabaseName SETSINGLE_USER
  2. Change the compatibility level of the database.
  3. Put the database in multiuser access mode by using ALTER DATABASE DatabaseName SETMULTI_USER

If you want to change your database Compatibility level, you can use stored proceduresp_dbcmptlevel for setting the database compatibility level. You can use below script for changing the Compatibility Level.

EXEC sp_dbcmptlevel @dbname = 'YourDatabaseName', @new_cmptlevel = 100;

@new_cmptlevel is the version of SQL Server with which the database is to be made compatible. Version is tinyint, with a default of NULL. The value must 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
100 = SQL Server 2008

In SQL Server 2008 you can user Alter Database script to change Database Compatibility Level, below is the script for that.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 60 |65 | 70 | 80 | 90 | 100 }

 

Advertisements

3 Responses to Compatibility in SQL Server

  1. After I originally left a comment I appear to have clicked on the -Notify me when new comments
    are added- checkbox and from now on each time a comment is added I recieve 4 emails with the same comment.
    Perhaps there is a means you can remove me from
    that service? Thank you!

    • dbamohsin says:

      This feature is controlled by WordPress. Go to an example email and there is usually an unsubscribe option at the bottom of the email.

  2. Alison says:

    This particular article, “Compatibility in SQL Server
    | Mohsin’s DBA Blog” ended up being very good. I am making out a replica to
    clearly show my friends. Many thanks,Jackson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: