SQL 2005 getting too smart?

We had a bug with one of our clients that had just upgraded from SQL Server 2000 to 2005. In the process, we were deploying some new application features to them, and one was crashing. This same feature was working just fine for other clients (and in-house, of course), so we were puzzled.

The problem is actually from something I used to wish SQL Server was smart enough to figure out on its own for a long time. Namely, when you write a SQL statement such as this:

Select U.UnitId, U.UnitName, UT.UnitTypeName
From Unit U 
Inner Join UnitType UT On U.UnitTypeId = UT.UnitTypeId
Order By UnitTypeId

you will get an error to the tune of "Ambiguous column name 'UnitTypeId'", because (in this case) you reference it in the Order By clause but don't indicate which source table you want, since the same column name exists in both tables referenced in the From clause.

Logically, it doesn't matter which one, because you mandate in the From clause that both UnitTypeIds are equal, but SQL syntax dictates you must specify which one you want.

Despite seeing this many times, it's still not automatic that I'll specify the table in my Select and Order By clauses, so I still see this error a lot.

I hadn't seen it recently, but suddenly it popped up on this one client's database. Sure enough, I hadn't specified the table in the Order By clause again. Yet it was working fine on other clients' databases, and working fine internally.

Our QA guy managed to find the "SQL Compatibility Mode" option on the database. Because they had upgraded this database from a SQL 2000 database, it was still in "SQL 2000" mode. Because of this, it was revealing the error in my SQL that was going through undetected on servers running in native SQL 2005 mode. He flipped the switch to "SQL 2005", and it let my bug go through.

Needless to say, my local database is now in the more restrictive "SQL 2000" mode, so hopefully I can catch more bugs before they're revealed by environment. It should also help if we ever decide to migrate to other database servers, such as MySQL.

No comments: