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.
