I’ve talked about Collation Confusion before. We had the dev and test instances at one collation and the production instance was another collation. Wow, was that a headache.

Well, we had a similar issue recently. This time we had a vendor reporting the following error:

Msg 207, Level 16, State 1, Line 7

Invalid column name ‘Name’

Now the vendor was certain this was a permissions issue. It worked fine on their systems, it worked fine on some of ours. So why didn’t it always work? Well, the easy answer is permissions! Particularly since we had denied them db_owner just recently.

So why do I sound so dismissive about permissions as a possibility? I mean it COULD be permissions. It certainly is possible. But first of all, we don’t use column level permissions very often (no one uses them all that often from what I can tell) and secondly it worked on several other systems where they had exactly the same permissions as this system.

Ok, so what is the problem? You guessed it! (I really have to stop asking for guesses after I’ve put the answer in the title.) Collation!

See here’s the thing. If your instance collation is case sensitive then tempdb is case sensitive. (The instance this DB got put on was case sensitive.)

This means that if you don’t specify the column collation it’s going to be case sensitive. Regardless of the database you are in a temp table uses the collation of tempdb.

Try running this code from a case-sensitive DB and from a non-case sensitive DB.

CREATE TABLE #temp (id int, name varchar(30));
GO
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME LIKE '%#temp%'
  AND COLLATION_NAME IS NOT NULL;
GO

In both cases, the results will be the same. The collation name will match the tempdb default.

Ok, but that isn’t going to cause that first error. That first error is caused by the fact that the system table collation is going to match the database collation. Run this on two different databases with different collations.

SELECT collation_name FROM sys.databases
WHERE database_id = DB_ID();
SELECT collation_name FROM sys.all_columns
WHERE object_id = object_id('sys.tables')
  AND [name] = 'name';

In both cases the DB collation and the colation of name in sys.tables is going to be the same.

So end result? If the instance is case sensitive, tempdb is case sensitive, the system tables of tempdb are case sensitive. When querying temp tables you have to treat them as if they are case sensitive. Because they are.

Filed under: Collation, Microsoft SQL Server, SQLServerPedia Syndication, System Databases Tagged: collation, microsoft sql server, tempdb database