Impact of TEMPDB Collations on Other Databases

Posted on : 02-01-2013 | By : Devi Prasad | In : Interview Questions, SQL Server Collations, T-SQL Code Examples, Temporary Tables

0



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

CREATE DATABASE TESTCOLLATION
COLLATE SQL_Latin1_General_CP1_CS_AS ;
GO

Let us create a sample table in a database and insert data.

USE TESTCOLLATION
GO

CREATE TABLE TestTable ( NAME VARCHAR(100))
GO

INSERT INTO TestTable
SELECT 'TestName'

GO

In the below queries let us query the table.

USE TESTCOLLATION
GO
SELECT * FROM TestTable

SELECT * FROM TestTable where NAME = 'TestName'

SELECT * FROM TestTable where NAME = 'testname'

GO

output:

NAME
----------------------------------------------------------------------------------------------------
TestName

(1 row(s) affected)

NAME
----------------------------------------------------------------------------------------------------
TestName

(1 row(s) affected)

NAME
----------------------------------------------------------------------------------------------------

(0 row(s) affected)

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.

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'

Output:


(1 row(s) affected)
NAME
----------------------------------------------------------------------------------------------------
TestName

(1 row(s) affected)

NAME
----------------------------------------------------------------------------------------------------
TestName

(1 row(s) affected)

NAME
----------------------------------------------------------------------------------------------------
TestName

(1 row(s) affected)

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:

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'

Output:

NAME
----------------------------------------------------------------------------------------------------
TestName

(1 row(s) affected)

NAME
----------------------------------------------------------------------------------------------------

(0 row(s) affected)

Cleanup:

DROP DATABASE TESTCOLLATION
GO



Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.



Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.




Your Ad Here