Correct way to set collation in temporary table column TSQL

Question

I have a temporary table which gets data inserted using bulk insert. However, when I want to update data from temp table to a normal table it gives collation problems. I know how to solve this by using something like:

UPDATE RegularTable
SET r.Column1 = t.ColumnA
FROM RegularTable r INNER JOIN #TEMP t ON
r.Column1 COLLATE DATABASE_DEFAULT = 
t.ColumnA COLLATE DATABASE_DEFAULT

But, is there a way to set the collation in the temporary table immediately so you don't have to use collate in the join? Something like:

CREATE TABLE #TEMP
Column1 varchar(255) COLLATE database_default,
Column2 varchar(60) 

Is this correct coding and do you have to set the collation once per table or per column? And if the collation is set in the table, can you exclude the collate from the join then?

Belvabelvedere

Answer 1

You can use COLLATE database_default in the temp table definition using the syntax you describe, and that will make each column collation-compatible with your database.

You have to set it explicitly per column. There is no table-level default collation. There is a database-level default collation, but for tempdb this is always equal to the default collation of the model database, which by default is the server collation.

If you set the collation on the table column, you can still override it in a query, as you have already experienced.

Waterlogged

Thanks @Christian Hayter, so if I set COLLATE database_default for each column I don't have to use the collate in the join anymore? Just out of curiosity, is there a way to see what the default server collation setting is? – Belvabelvedere

@J3FFK: That is correct. As for viewing the current collation, I'm sure there is a T-SQL function to get it, but I normally just bring up the database properties dialog in Management Studio. :-) – Waterlogged

@J3FFK: You can read the db collation from master.sys.databases.collation_name – Waterlogged

Thanks! I see it in the properties window in SSMS, I used the collate in both columns and it works great, don't have to use it in the join anymore which is easier. – Belvabelvedere

Answer 2

We ran into the same problem right now. Instead of adding the collation to each temp table join, we just changed the temp table creation to a table variable declaration.

Dessert

That is the easiest way – Theft