Compatibility Mode 80 versus 90
When we upgraded from 2000 to 2005, we didn't realize that our databases compatibility modes would stay at 80. Newly created databases in SQL Server 2005 will be at Compatibility Mode 90, but upgraded databases stay at Compatibility Mode 80. I found out the hard way that this was for safety’s sake.
I discovered that my upgraded databases were still in 80 Compatibility Mode when I tried to run one of SSMS's cool new Summary Reports and it complained that it couldn’t display the report because the database's Compatibility Mode was 80 and it needed to be 90. Oh well, no biggie. I figured, I'll just pop in and change that. After all, it's a quick fix. Famous last words!
As soon as I switched it, apps started dying. Eeeek! Errors over here and errors over there. Errors everywhere! The error messages all said ‘Ambiguous column name’.
Here's what I found out.
When you switch to Compatibility Mode 90, you're basically telling SQL Server to recognize all the new keywords and such. You're also telling SQL Server to behave a little differently, i.e. as SQL Server 2005 would behave. One of the new behaviors of SQL Server 2005 is that it wants you to fully specify your columns in your ORDER BY clause if you select a column name more than once in a SELECT statement.
Here’s an example of a query that SQL 2005 didn’t like with the new 90 Compatibility Mode:
SELECT EmployeeName, EmployeeName
FROM (select 'John' as EmployeeName) tmp
ORDER BY EmployeeName;
A simple fix was to remove the extra column (it was in there twice by mistake anyway). If we really wanted to have it in there twice (you never know), we could have also just fully qualified the column name with the table name, like so:
SELECT EmployeeName, EmployeeName
FROM (select 'John' as EmployeeName) tmp
ORDER BY tmp.EmployeeName;
Interesting gotcha, especially because the error message didn’t exactly say, hey, you changed the Compatibility Mode and now I want you to be more precise with your column names in your ORDER BY clauses. Oh how I wish error messages would really tell me what’s wrong. But then, if they did, oh what fun I’d miss out on interpreting them!
More info on compatibility levels can be found here:
http://msdn2.microsoft.com/en-US/library/ms178653(SQL.90).aspx
I hope this gotcha helps someone. I’ve got plenty more to come!
