solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Upgrading SQL Server databases and changing compatibility levels

MSSQLTips author Greg Robidoux By:   |   Read Comments (10)   |   Related Tips: More > Upgrades and Migrations

Problem
When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your databases still act as though they are running using an earlier version of SQL Server.  From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibly level is changed.  This tip will show you how to check the current compatibly level, how to change the compatibly level and also some of the differences between earlier versions and SQL Server 2005.

Solution
The first thing that you need to do is to check the compatibility level that your database is running under.  As mentioned above any database that is upgraded using the backup and restore or detach and attach method will not change the compatibly level automatically, so you will need to check each database and make the change.

Although SQL Server has changed its naming convention to SQL Server 2000, 2005 and soon to be released 2008 the internal version numbers still remain.  Here is a list of the compatibly levels (versions) that you will see:

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

Identifying Compatibly Level

To check the compatibility level of your databases you can use one of these methods:

Using SQL Server Management Studio, right click on the database, select "Properties" and look at the "Options" page for each database as the following image shows:

Another option is to use sp_helpdb so you can get the information for all databases at once:

EXEC sp_helpdb

Or select directly from the sys.databases catalog to get the information for all databases.

SELECT * FROM sys.databases


Compatibly Level for New Databases

When issuing a CREATE DATABASE statement there is not a way to select which compatibility level you want to use.  The compatibility level that is used is the compatibility level of your model database.

Here is a sample CREATE DATABASE command, but there is not an option to change the compatibility level.

CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'Z:\SQLData\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'Y:\SQLData\test3_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%)
GO

When creating a database using SQL Server Management Studio you have the ability to change the compatibility level on the "Options" tab such as follows:

If we use the "Script" option we can see that SQL Server issues the CREATE DATABASE statement and then issues "sp_dbcmptlevel" to set the database compatibility level to 80 as shown below.

CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'Z:\SQLData\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'Y:\SQLData\test3_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=80
GO

Changing Compatibility Level

So once you have identified the compatibility level of your database and know what you want to change it to you can use the sp_dbcmptlevel system stored procedure to make the change.  The command has the following syntax:

sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]

--to change to level 80
dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=80

--to change to level 90
dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=90

--or
sp_dbcmptlevel 'test', '80'

sp_dbcmptlevel 'test', '90'


Differences

There are several differences on how compatibly levels affect your database operations.  SQL Server Books Online has a list of these differences and the following list shows you a few of these items:

Compatibility level setting of 80 or earlier Compatibility level setting of 90 Possibility of impact
For locking hints in the FROM clause, the WITH keyword is always optional. With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL). High
The *= and =* operators for outer join are supported with a warning message. These operators are not supported; the OUTER JOIN keyword should be used. High
SET XACT_ABORT OFF is allowed inside a trigger. SET XACT_ABORT OFF is not allowed inside a trigger. Medium

(Source: SQL Server 2005 Books Online)  For a complete list of these items look here:

In addition, each new compatibility level offers a new list of reserved keywords.  Here is a list of the new keywords for SQL Server 2005.

Compatibility level setting Reserved keywords
90 PIVOT, UNPIVOT, REVERT, TABLESAMPLE
80 COLLATE, FUNCTION, OPENXML
70 BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP
65 AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, WORK

(Source: SQL Server 2005 Books Online)

If one of these keywords is being used and your database is set to this compatibly level the commands will fail.  To get around this you could put the keyword in either square brackets ([ ]) or use quotation marks (" ") such as [PIVOT] or "PIVOT".


Summary

The compatibly level setting is used by SQL Server to determine how certain new features should be handled.  This was setup so you could migrate your databases to a later release of SQL Server without having to worry about the application breaking.  This setting can be changed forward and backwards if needed, so if you do change your compatibly level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.

In addition, there are certain features that only work if the database is set to the latest compatibly level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibly level.

 

Next Steps

  • Check your database compatibly level.  I have seen many databases that have been migrated to SQL Server 2005, but the compatibly level has not been changed.
  • If you are unsure if there will be issues by changing your compatibly level take a look at this information to see what the impact may be.


Last Update: 2/18/2008


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, February 18, 2008 - 12:13:21 PM - Malc Read The Tip

There are also issues with upping the compatibility level from a previous SQL version to version 90 - you need to also check that your code still works with the stricter rules under SQL 2005.  For an explanation take a look at this:

 http://www.innovartis.co.uk/topical/migratingto2005.aspx

 


Monday, February 18, 2008 - 5:22:23 PM - grobido Read The Tip

This is true there may be issues with upping the compatiblity level, but these items should be reviewed and corrected.  I have seen many implementations of SQL Server 2005 where databases were migrated, but the compatiblity levels were never changed.  I guess if someone is not using the newer features they wouldn't notice, but at some point they would probably run into a need where they would have to up the compatiblity level.


Tuesday, February 19, 2008 - 1:05:24 AM - Malc Read The Tip

Actually you the problems would specifically concern the T-SQL that worked perfectly fine under 2000 but does not work when the compat is changed to 90 i.e. after upping the level the database could be broken in some way.  It's finding the issues amongst all the stored procedures, functions etc. that is the arduous part unless you have a 100% coverage automated regression test pack.  That's why we recommend rebuilding your database to see what doesn't compile at level 90...


Monday, October 29, 2012 - 5:44:17 AM - Mike del Rosario Read The Tip

I installed MS SQL SERVER 2008 R2 in my server and need to create a new database which compatibility level should be set to at least 90. However, the dropdowlist in the compatibility level of the database option does not include this level but 70 and 80 only.  Is there a way that I can make this option available? I was expecting that MS SQL SERVER 2008 R2 installation would include this level already. Hope to hear from you about it.

 

Thanks!

 

Mike


Monday, October 29, 2012 - 8:52:17 AM - Greg Robidoux Read The Tip

@Mike del Rosario - are you sure you are connecting to the correct SQL Server instance?  For SQL 2008 R2 you should see 80, 90 and 100.  If you are connected to a SQL Server 2000 instance you would see 70 and 80.


Sunday, June 02, 2013 - 5:52:44 AM - Sandeep Read The Tip

 

If I install sql 7.0 ,upto what compatibilty level it will support ?Means 80 and 90 or 80,90,100


Sunday, June 02, 2013 - 6:28:27 AM - Greg Robidoux Read The Tip

@Sandeep - SQL Server 7.0 would be up to compatibilty level 70


Wednesday, June 19, 2013 - 5:38:51 AM - vikar Read The Tip

We upgraded our DB server from 2000 to sql server 2008 and changed to compatibility level to 2008, but when i look at one of my application pages, the formatting is considerably changed (especially the table <tr> and <td> tags). When i revert back the comp level back to 2000, the page looks fine again.

My query is : how can DB server upgrade(and subsequent comp level upgrade) affect the web form HTML tags?? But this is exactly what is happening here because nothing else is touched here except the db server upgrade and comp level upgrade from 2000 to 2008.

 

Any idea guys? Thanks a lot in advance!


Wednesday, June 19, 2013 - 5:43:05 AM - vikar Read The Tip

To be very precise -- the code that is shifting in my page after db server upgrade and compatibilty level changed from 2000 to 2008 , is shown below:

 

<table cellspacing=0 cellpadding=0 width=100% style="padding-right: 500px; line-height:20px">
  <tr>
   <td colspan="2"><h1  class="h2">HR Team</h1></td>
  </tr>

</table>


Wednesday, August 07, 2013 - 3:29:29 AM - rajesh Read The Tip

ALTER DATABASE ['

+ @DatabaseName +

'] SET COMPATIBILITY_LEVEL = 80'



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.