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

up vote 1 down vote accepted

would this script use a temporary table? Then the table will have the server default collation.

If it just a quick one-time solution, then I would modify the source query to provide the data in the correct collation, here Hebrew_CI_AS

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:

  1. 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.
  2. 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.

You can use COLLATE database_default to solve this types of problem

Collate Is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.

Syntax

COLLATE { <collation_name> | database_default }

collation_name Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified Windows_collation_name or a SQL_collation_name. collation_name must be a literal value. collation_name cannot be represented by a variable or expression.

database_default Causes the COLLATE clause to inherit the collation of the current database.

SELECT * FROM test1 INNER JOIN test2  on test1.col COLLATE 
database_default = test2.col COLLATE database_default

For more about https://msdn.microsoft.com/en-us/library/ms184391.aspx

  • why down vote? its work perfect for me – GuRu Mar 17 '16 at 13:57
  • The OP asks what is the reason the server collation is used instead of column collation. – vonPryz May 13 at 6:06

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.