Wednesday, March 29, 2006

SQL Server 2005 Gotcha #1

So, my company, SFA, jumped into SQL Server 2005 with both feet. We actually upgraded to it on February 3rd, so we've been running it almost 2 months now, and I've decided to share some gotchas that I ran into, in hopes that you won’t have to repeat my mistakes.

SQL Server 2005 Gotcha #1
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!

11 comments:

Cliff said...

I almost suffered from the order by gotcha. I encountered it in testing and your blog confirmed it.

Another one was with FOR XML AUTO and nested selects. Let's say you did this for whatever reason:

select Field1, FirstName, LastName
FROM (Select 'Extra' Field1, PC.FirstName, PC.LastName from Person.Contact PC WHERE ContactId = 1) PersonContact
FOR XML AUTO

In compatibilty mode 90 you get

<PersonContact Field1="Extra" FirstName="Gustavo" LastName="Achong" />

But in compatibility mode 80 you get

<PersonContact Field1="Extra">
<PC FirstName="Gustavo" LastName="Achong" />
</PersonContact>

The funny thing is that in SQL Server 2000 it comes back the first way (the 90 compatibility way).

I'm not sure if this is different in SP1 or not.

Emanuele Ornella said...

Do you run the Upgrade Advisor just before to upgrade from 2000 to 2005 (mode 80)?
I suppose the UA would adivse you about the ORDER BY issue.
Bye,
ema

David E. Patrick said...

Yep, we ran the Upgrade Advisor before actually upgrading and it did give me this warning about ORDER BY:

"Column aliases in ORDER BY clause cannot be prefixed by table alias"

but, this isn't the same thing that I ran into. I actually fixed my issue by adding a table prefix to the offending columns, whereas this issue is fixed by removing the prefix.

Philip Lee said...

We ran into an additional issue where views that contain ORDER BY clauses behave differently in 80 vs. 90 mode.

Specifically, in 90 mode, the ORDER BY only impacts the order in which records are evaluated for a TOP clause, and the ORDER BY clause does not control the order in which records are returned.

For example, if the SQL in a view reads as:

SELECT TOP 100 PERCENT
Employees.EmployeeID
, Employees.FullName
FROM
Employees
ORDER BY
Employees.LastName

In 90 mode running a SELECT against this view will not yield results coming back ordered by LastName. In 80 mode it will.

That's a fun little change that got buried in the 05 release notes.

Anonymous said...

I started a migration yesterday from Access to SQL Server and this was one of those gotchas that isn't listed in a table anywhere. The following query works in access:

SELECT YEA_TITLE, YEA_TITLE FROM YEARS WHERE YEA_STATUS = 'A' ORDER BY YEA_TITLE DESC

It doesn't work in SQL Server as written. I received an "Ambiguous column" error. I didn't know immediately if my problem was having a column selected twice or in the ORDER BY statement. I changed it to the following and it works like a charm:

SELECT YEA_TITLE, YEA_TITLE FROM YEARS YEA WHERE YEA_STATUS = 'A' ORDER BY YEA.YEA_TITLE DESC

Before you ask (BYA!), I am intentionally selecting two columns b/c this query is part of a passed argument to a function designed to show drop-downs from queryies. In 99% of the cases, the two columns identify an ID (Option Value) and a Title for the Option Label. Years is one of the few cases where the primary key of the table is actually the label I want users to see. Even for States and countries, the first column would be the ABBREVIATION and the second column would be the TITLE. It's juts one of those weird quirks that I'm glad I found a way around - correction, that David found for me!

- Nick Owens (VehicleMLS.com)

Bruno Coelho said...

I've installed SQL Server 2005 with SP1 and SP2 and I don't have the compatibility mode 90 even if I create a new database.

When I enter SQL Server Management Studio it says: SQL Server 8.0.194.

Any ideas?

Thanks!

David E. Patrick said...

Hi Bruno!

SQL Version 8.0.194 is version 2000 without any service packs, so it sounds like you're looking at the wrong instance. When you installed 2005, I'm guessing it created a new instance, that you have to reference specifcally. If your server was named LONDON, it would be something like LONDON\SQL2005, assuming you accepted the default name for the new instance.

Does this help any?

- Dave

ghkj said...
This comment has been removed by a blog administrator.
Anonymous said...

We're getting some serious slow downs in our upgrade from 2000 to 2005. nothing mentioned here was missed, so golly gee, maybe i need to power level my wow characters. what a crock.

Spam sucks.

David E. Patrick said...

We also suffered slow downs at first coming up from 2000 to 2005. Recent service packs and hot fixes helped. Are you running the latest SP? - Dave

vfdvgf said...

The World Leading wow power leveling and wow gold wow power leveling