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:
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:
Turning my comments to answer :
SSMS is just a tool to connect to SQL Server.
You did an inplace upgrade, but were connecting to SQL Server 2012 using SSMS 2008R2 and SSMS 2012.
Using T-SQL - will give you the correct version:
SELECT
SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
SERVERPROPERTY ('ServerName') AS SQLServerName,
SERVERPROPERTY ('Edition') AS ServerEdition,
CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) + ' + '
+ CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
+ CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
SERVERPROPERTY ('Collation') AS Collation
Best practice:
- Before upgrade - run the above script to get the version and after upgrade, run it to make sure that you are on correct right version.
- Always use the latest version of SSMS (regardless of SQL Server version).
Best Answer
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.