Default collation of temporary tables

sql sql-server tsql collation temp-tables
gotqn picture gotqn · Mar 10, 2012 · Viewed 8.9k times · Source

How can I check what the collation of a temporary table is?

I want to do this because I want to check what happens if I have database with a specific collation, different from the SQL Server instance and create a temporary table.

Let's say we have this scenario:

SQL Server 2008 - Latin1_General_CS_AS

Test Database - Estonian_CS_AS

Then create table #Test without specifying the collation. Which will be the collation of the table?

I think Estonian_CS_AS, but in the test I am doing is said Latin1_General_CS_AS. That's why I need to find a SQL statement to check this.

Note: from what I have read, I think that the collation of a temporary objects is defined by the tempdb collation. But if this is true, what defines its kind?

Answer

Mikael Eriksson picture Mikael Eriksson · Mar 10, 2012

tempdb is recreated when the server start and gets the collation from the model database.

Temp tables you create without specifying the collation will have the collation of tempdb. When creating temp table you can use database_default as collation to get the same collation as the current database.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More