Collation Conflict - 3

I posted a blog post about Collation Conflict some time back. With the discussions on Temp tables and table variables we realized that we have an option with table variable as work around for the above problem.

However, we have much cleaner option in SQL Server 2012 by using contained database option.

Let us create the Contained database.

CREATE DATABASE [Danish]
CONTAINMENT = PARTIAL
COLLATE Danish_Norwegian_CI_AS
GO

Then let me run the previous script which failed in standard databases.

USE Danish
GO

CREATE TABLETable1
(IDINT IDENTITY,
NameVARCHAR(50)
)

INSERT INTOTable1
(NAME)
VALUES
('A'),
('B'),
('C')

CREATE TABLE#Temp1
(MaxIDNAME VARCHAR(50)
)

INSERT INTO#Temp1
SELECTMAX(Name)FROMTABLE1

SELECT*FROMTABLE1
WHERENAMEIN
(SELECTMAxIDNAME from#Temp1)

This time we don’t have any errors.

Actually, temp table  is created in the tempdb but will not have an issue as in the standard databases.

Collation Conflict 2

I posted a blog post about Collation Conflict some time back. With the discussions on Temp tables and table variables are on let us do this with a table variables.

Let us prepare the data set needed.

Then run the following using a table variable instead of temp table.

and this it is a success ( With using temp table, this will fail since tempDB in different collation)

So another place to use table variable and the debut continues …

Collation is Missing when Scripting the Database

You might know that you are not allow to modify the Collation after creating a database.

So you use the scripting option for the database you will see that collation is missing in the script.

Following is the script database create with Danish_Norwegian_CI_AS collation.

However, you can see that collation is not mention in the above script.

If you want to script you need to explicitly mention the COLLATE as shown below.

Collation Conflict

You can create a database with a different collation to the server collation. This is one issue you will face in case database collation is different from the server collation.

Then let us create a table with few data,

Let me use temp table to use this data.

So you are using temp table and actual table. (This is a hypothetical scenario just to present this idea).

Last query will return following error.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation.

This is due to the temp database has the server collation, so the workaround will be at the time of creating temp table, you have to explicitly mention the collation.