SQL Server Compatibility Levels

sqlserver2014

With SQL Server 2014, there is a new compatibility level. You can operate SQL Server in a number of different compatibility levels, but you must change it from the default. These compatibility levels reflect the different versions of SQL server:

  • 80 = SQL Server 2000 (Supported in SQL Server 2008 and SQL Server 2008 R2 or lower)
  • 90 = SQL Server 2005 (Supported in SQL Server 2008 through SQL Server 2012)
  • 100 = SQL Server 2008 and SQL Server 2008 R2 (Supported in SQL Server 2008 through SQL Server 2014)
  • 110 = SQL Server 2012 (Supported in SQL Server 2012 through SQL Server 2014)
  • 120 = SQL Server 2014 (Supported in SQL Server 2014)

Changing the compatibility level in SQL Server may change the behavior or the functionality of the SQL Server instance. Make sure you research and test the impact of changing compatibility levels before you make any change.

Here is a sample script that will change the current compatibility level:
ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 | 110 | 120 }
The best way to perform this type of change:
  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.
To set the compatibility level to 110, then show you the current level after it has been set:
ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = 110;
GO
SELECT compatibility_level FROM sys.databases WHERE name=dbname();
GO

You can read more on the subject here.

Advertisements

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