| « A Place to Gather Intelligence on 1-800 Callers | Isolated Big Baby Syndrome » |
SQL Server 2000 Gotcha With Uniqueidentifiers
09/15/07
SQL Server 2000 Gotcha With Uniqueidentifiers
If you're considering using uniqueidentifiers on your tables, particularly as primary keys, then you might want to upgrade your SQL Server or just check your SQL carefully if you're still running SQL Server 2000.
In particular, doing a COUNT(field) on a uniqueidentifier comes up with a bizarre error on SQL Server 2000:
Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.
It's truly a weird error. Why on earth would there be difficulties in counting any field?
Now you can go COUNT(*) without difficulty, or use a different field. COUNT(field) is useful when you're doing cross-database SQL, since it's actually faster than COUNT(*) on some databases, but if you can tune your SQL to the particular database, it helps. SQL Server 2005 also combats this silliness, but this only helps if you can deprecate support for SQL Server 2000.
Just wanted to post this, since there really wasn't anything helpful around the 'Net on that particular error message.
1 comment
Thanks for putting up this info about the SQL error. It must have been because I was trying to write my join late on a Friday that my brain was fried and I couldn’t figure out my problem. Hooray, I fixed it :)
