SQL Server case/collation issue

Question

I had a weird situation in a clients database today. SQL Server 2005, database collation is case-insensitive, so I can write SQL queries using any case, without any problem ... except one. One particular column, in one particular table, is called 'DeadZone'. If I query like this: 'select Deadzone from TableName' - I get a column not found error like this: 'select DeadZone from TableName' - it works. Now the kicker, like this: 'select deadZone from TableName' - it works!

So only the 'Z' in the column name is case-sensitive! I created a similar column name in the same table, with a 'Z' in it, and that column isn't case-sensitive.

Any ideas? I'm sure I can work around this, probably by dropping and re-adding the column, but it would be good to get to the bottom of this one. BTW, the database is on a PC in Hungary, so is it possible that some Hungarian-specific character issues are causing this? Remembering that another column name with a 'Z' in it did not have the same issue.

-Graeme

Answer 1

What is the exact collation? Hungarian does have a special Dz letter, which might be collated differently from D - so your problem might actually be d, not z.

Deadzone is [D][e][a][dz][o][n][e]
DeadZone is [D][e][a][d][Z][o][n][e]
deadZone is [d][e][a][d][Z][o][n][e]

I'd vote you up +1000 for weirdest thing I've seen this month if I could.

See related issue with LY: http://www.sqlservercentral.com/Forums/Topic19439-9-1.aspx

And I don't think dropping and re-adding your column is going to help. You should probably only use real Hungarian column names if you are going to use a Hungarian database collation!