When you create databases with different collations then you have to be very careful when using the temporary tables.
In the below script let us analyze such a scenario.
Creating Sample database with case sensitive collation
[sql]
CREATE DATABASE TESTCOLLATION
COLLATE SQL_Latin1_General_CP1_CS_AS ;
GO
[/sql]
Let us create a sample table in a database and insert data.
[sql]
USE TESTCOLLATION
GO
CREATE TABLE TestTable ( NAME VARCHAR(100))
GO
INSERT INTO TestTable
SELECT ‘TestName’
GO
[/sql]
In the below queries let us query the table.
[sql]
USE TESTCOLLATION
GO
SELECT * FROM TestTable
SELECT * FROM TestTable where NAME = ‘TestName’
SELECT * FROM TestTable where NAME = ‘testname’
GO
[/sql]
output:
[text]
NAME
—————————————————————————————————-
TestName
(1 row(s) affected)
NAME
—————————————————————————————————-
TestName
(1 row(s) affected)
NAME
—————————————————————————————————-
(0 row(s) affected)
[/text]
It can be found that the third select statement above does not return any rows as the database and the columns in the table are with case sensitive collation.
Now let us create a temp table with same data as above and check the results.
[sql]
USE TESTCOLLATION
GO
CREATE TABLE #TestTable ( NAME VARCHAR(100))
GO
INSERT INTO #TestTable
SELECT ‘TestName’
GO
SELECT * FROM #TestTable
SELECT * FROM #TestTable where NAME = ‘TestName’
SELECT * FROM #TestTable where NAME = ‘testname’
[/sql]
Output:
[text]
(1 row(s) affected)
NAME
—————————————————————————————————-
TestName
(1 row(s) affected)
NAME
—————————————————————————————————-
TestName
(1 row(s) affected)
NAME
—————————————————————————————————-
TestName
(1 row(s) affected)
[/text]
It can be found that the third select statement above gives ‘TestName’ as output.
So when we go with an assumption that the temporary tables have the same collation as the database in which it is created then the code would fail.
Resolution:
[sql]
SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘TestName’
SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘testname’
[/sql]
Output:
[text]
NAME
—————————————————————————————————-
TestName
(1 row(s) affected)
NAME
—————————————————————————————————-
(0 row(s) affected)
[/text]
Cleanup:
[sql]
DROP DATABASE TESTCOLLATION
GO
[/sql]