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:
This may produce some false positives (e.g. you may have *=
in a comment), but should be a good start:
SELECT
obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
+ '.' + QUOTENAME(o.name),
o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';
Best Answer
For this number of jumps in compatibility level (or if you are being appropriately paranoid with a production application, for any jumps in compatibility level) a full application regression test is the only way to be absolutely sure.
Even if you have ensured that all the matters listed in the release notes either don't affect you or have been mitigated, you might find instances where code is relying on officially undefined behaviour which has changed (changes like this may not appear in release notes). For instance, when moving some of our work from 2005 to 2008r2 we experienced problems with a change in how some (admitedly odd) queries using
FOR XML
generated their output.