When you create databases with different collations then you have to be very careful when using the temporary tables.<\/p>\n
In the below script let us analyze such a scenario.<\/p>\n
Creating Sample database with case sensitive collation
\n[sql]
\nCREATE DATABASE TESTCOLLATION
\nCOLLATE SQL_Latin1_General_CP1_CS_AS ;
\nGO
\n[\/sql]<\/p>\n
Let us create a sample table in a database and insert data.
\n[sql]
\nUSE TESTCOLLATION
\nGO<\/p>\n
CREATE TABLE TestTable ( NAME VARCHAR(100))
\nGO<\/p>\n
INSERT INTO TestTable
\nSELECT ‘TestName’<\/p>\n
GO<\/p>\n
[\/sql]<\/p>\n
In the below queries let us query the table.
\n[sql]
\nUSE TESTCOLLATION
\nGO
\nSELECT * FROM TestTable<\/p>\n
SELECT * FROM TestTable where NAME = ‘TestName’<\/p>\n
SELECT * FROM TestTable where NAME = ‘testname’<\/p>\n
GO
\n[\/sql]<\/p>\n
output:
\n[text]
\nNAME
\n—————————————————————————————————-
\nTestName<\/p>\n
(1 row(s) affected)<\/p>\n
NAME
\n—————————————————————————————————-
\nTestName<\/p>\n
(1 row(s) affected)<\/p>\n
NAME
\n—————————————————————————————————-<\/p>\n
(0 row(s) affected)
\n[\/text]<\/p>\n
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.<\/p>\n
Now let us create a temp table with same data as above and check the results.<\/p>\n
[sql]
\nUSE TESTCOLLATION
\nGO<\/p>\n
CREATE TABLE #TestTable ( NAME VARCHAR(100))
\nGO<\/p>\n
INSERT INTO #TestTable
\nSELECT ‘TestName’<\/p>\n
GO<\/p>\n
SELECT * FROM #TestTable<\/p>\n
SELECT * FROM #TestTable where NAME = ‘TestName’<\/p>\n
SELECT * FROM #TestTable where NAME = ‘testname’
\n[\/sql]<\/p>\n
Output:
\n[text]<\/p>\n
(1 row(s) affected)
\nNAME
\n—————————————————————————————————-
\nTestName<\/p>\n
(1 row(s) affected)<\/p>\n
NAME
\n—————————————————————————————————-
\nTestName<\/p>\n
(1 row(s) affected)<\/p>\n
NAME
\n—————————————————————————————————-
\nTestName<\/p>\n
(1 row(s) affected)<\/p>\n
[\/text]<\/p>\n
It can be found that the third select statement above gives ‘TestName’ as output.<\/p>\n
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.<\/p>\n
Resolution:
\n[sql]
\nSELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘TestName’<\/p>\n
SELECT * FROM #TestTable WHERE NAME collate SQL_Latin1_General_CP1_CS_AS = ‘testname’
\n[\/sql]<\/p>\n
Output:
\n[text]
\nNAME
\n—————————————————————————————————-
\nTestName<\/p>\n
(1 row(s) affected)<\/p>\n
NAME
\n—————————————————————————————————-<\/p>\n
(0 row(s) affected)<\/p>\n
[\/text]<\/p>\n
Cleanup:
\n[sql]
\nDROP DATABASE TESTCOLLATION
\nGO
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[640,1742,1789,759],"tags":[],"class_list":["post-260","post","type-post","status-publish","format-standard","hentry","category-interview-questions","category-sql-server-collations","category-t-sql-code-examples","category-temporary-tables"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=260"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/260\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=260"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}