If you do not specify a collation when creating a table (regular or temporary or variable), then the database's default collation is used. When creating a temporary table (not table variable), it exists in tempdb, so tempdb's default collation is used. TempDB's default collation is derived from the server default since the server default was used to create the model database which is in turn used to create all new databases, including tempdb.
Fortunately, you can use the COLLATE clause to specify which collation to use for the column(s), regardless of any database's default collation. And if you are creating temporary tables in databases that have different collations and you don't always know ahead of time what the collation will need to be, then you can specify DATABASE_DEFAULT which will use the current database's default collation instead of tempdb's default collation.
You can see this behavior by running the following tests. The setup creates a database with a collation that I assume is not your server's default (so that it can be obviously different from tempdb).
Test Setup
USE [master];
IF (DB_ID(N'CollateEBCDIC') IS NULL)
BEGIN
CREATE DATABASE [CollateEBCDIC]
COLLATE SQL_EBCDIC277_CP1_CS_AS;
END;
GO
Test Queries
1) Creating a table without specifying the COLLATE clause will use that database's default collation:
USE [CollateEBCDIC];
CREATE TABLE dbo.TestCollate (Col1 NVARCHAR(50));
SELECT name, collation_name
FROM sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'dbo.TestCollate');
Returns:
name collation_name
Col1 SQL_EBCDIC277_CP1_CS_AS
2) Creating a temporary table without specifying the COLLATE clause will use tempdb's default collation:
-- DROP TABLE #TestCollate1;
CREATE TABLE #TestCollate1 (Col1 NVARCHAR(50));
SELECT name, collation_name
FROM tempdb.sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate1');
Returns:
name collation_name
Col1 SQL_Latin1_General_CP1_CI_AS
3) Creating a temporary table with specifying the COLLATE clause will use the specified collation. If you don't always know what that collation will be, you can specify DATABASE_DEFAULT which will use that database's default collation:
-- DROP TABLE #TestCollate2;
CREATE TABLE #TestCollate2 (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT);
SELECT name, collation_name
FROM tempdb.sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'tempdb.dbo.#TestCollate2');
Returns:
name collation_name
Col1 SQL_EBCDIC277_CP1_CS_AS
4) Declaring a table variable without specifying the COLLATE clause will use the current database's default collation:
SELECT name, collation_name
FROM sys.dm_exec_describe_first_result_set(N'
DECLARE @TestCollate3 TABLE (Col1 NVARCHAR(50));
INSERT INTO @TestCollate3 (Col1) VALUES (NCHAR(42));
SELECT * FROM @TestCollate3;
', NULL, NULL);
Returns:
name collation_name
Col1 SQL_EBCDIC277_CP1_CS_AS
Test Cleanup
DROP DATABASE [CollateEBCDIC];
Contained Database Wackiness
When working within a Contained Database, there are two differences from the behavior noted above:
- Temporary Tables use the Database's default Collation (i.e.
DATABASE_DEFAULT) for string columns that are not declared using the COLLATE keyword. Test #2 shows Temporary Tables using the Instance's default Collation.
- Database-level meta-data (e.g. names of objects, Users, Indexes, columns, etc) always has a Collation of
Latin1_General_100_CI_AS_KS_WS_SC (i.e. CATALOG_DEFAULT) regardless of the Database's default Collation.
