DB collation is used for comparison instead of column collation I am moving data from one SQL Server to another. lets call them source and destination SQL server.

The source SQL Server has collation SQL_Latin1_General_CP1_CI_AS and the destination has collation Hebrew_CI_AS.

The destination's SQL Server's tables and their columns have collations SQL_Latin1_General_CP1_CI_AS - which is good.

I asked source SQL Server to generate script for moving some tables. When I run that script on the destination SQL Server I get an error:


  Cannot resolve the collation conflict between
  "SQL_Latin1_General_CP1_CI_AS" and "Hebrew_CI_AS" in the equal to
  operation.


Why is it that the collation Hebrew_CI_AS is used (SQL Server Collation) and not the collation of the column?

Thanks
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.