Understanding MSSQL Compatibility Level – A Comprehensive Guide

compatibility-levelsql serversql-server-2005sql-server-2012upgrade

I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012.
So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).

Executed query on New database:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';

Result:

compatibility_level
-------------------
90

But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.

Let me conclude my problems with below questions:

If Compatibility_level = 90 is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?
Why does the restored database show Compatibility_Level as '90' and not '110'?
Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?

Is the upgrade as simple as "ALTER DB"? Like this:

ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';

Best Answer

Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?

If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.

Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?

When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance. In this case, 90 is still functional on the 2012 instance, so it was left as-is.

Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?

Upgrade is as much simple as "ALTER DB"?? like

Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.

You can remain at the lower compatibility level as long as you like. However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels. You can see a list of some of the changes here:

Differences Between Lower Compatibility Levels and Levels 110 and 120

Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available. So the next time you upgrade, your database will automatically be updated to compatibility level 100. You can see a table of supported levels on each engine version here:

ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments

Related Solutions

SQL Server 2012 – Upgrade Database Using Compatibility Level

Compatibility level simply changes the language syntax that is supported by the SQL Statements. This will cause all the execution plans to be expired and force them to be recompiled. It has nothing to do with the data storage engine or the way that the data is written to the disk.

Once a database is attached to a SQL 2012 instance, it is in the SQL 2012 storage format and can't be moved back to an older version of SQL (no matter what setting the compatibility level is set to).

The link provided by @Shark (http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.110%29.aspx) in the comments provides some additional reading on the subject.

Sql-server – How to detect compatibility errors – SQL Server 2012

Can you show the query? Just because it runs doesn't mean it's correct. :-) For example this is not legal, but it works:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY o.modify_date;

Technically, it should be as follows, since o.modify_date is not in the SELECT list:

SELECT CONVERT(SMALLDATETIME, modify_date) AS modify_date
FROM sys.objects AS o
ORDER BY modify_date;

In this Connect bug, it was stated that this rule would be removed from the Upgrade Advisor. I suspect that one of the following (or both) happened:

They forgot to remove the rule as they said they would
They had other complications which prevented the above syntax from being blocked by the parser

That all said, there are some breaking changes the upgrade advisor will never catch. For example, this will work on 2005, 2008 and 2008 R2:

CREATE TABLE #foo(id INT);

IF OBJECT_ID('tempdb..#foo') > 0
BEGIN
  DROP TABLE #foo;
END
GO

CREATE TABLE #foo(id INT);

This will break in SQL Server 2012, however, since #temp tables now get a negative object_id. The proper way to test is:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL

I go over a few other issues in this blog post. A couple of other breaking changes:

If you use any of the memory-related DMVs there has been a major overhaul to some of the columns:

https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/

Again, most of these are not caught by the upgrade advisor, so running that tool and not doing any thorough testing could really put you in a bad spot.

And I agree with Eric's answer - you should try to track deprecated events. For completeness, here are three approaches:

Best Answer

Related Solutions

SQL Server 2012 – Upgrade Database Using Compatibility Level

Sql-server – How to detect compatibility errors – SQL Server 2012

Related Question