Understanding importance of Collation in SQL Server

Good evening friends.. Last Friday I got a call from one of my friend and he asked for help on one problem which he was facing while writing a TSQL code. We was getting the bellow error message while executing his TSQL code.

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.

He was running the above query in the management studio and was getting the collation error, finally we got resolved the issue. Hence I thought to share this article with all of my friends.

Importance of collation :- Collation is used for comparing data and for sorting purpose in uni-code or non uni-code data types. When we install SQL Server it picks the collation setting from the windows system.

If your systems language setting is set to US English then your collation for SQL Server would be "SQL_Latin1_General_CP1_CI_AS" .

If you are comparing two columns of two different tables with different collations, you will be getting the same error message that I have mentioned earlier in this article.

Let me prove this with example.

I have created a test database, when I right click on the test database and click on properties menu, the above screen will pop up in front of us.I have encircled collation parameter with blue color. If I create a table by default table will be having collation "Hungarian_technical_100_CS_AS". Lets try and see.

I have created a table Person and run the below query "sp_help Person" that gives us the full information about the Person table. I  have encircled the collation with Blue color, you can see that collation for "Name" column is same as database has. Now lets create a temporary table and insert few rows in it.

Since temporary table is created in the tempdb, hence I have selected the tempdb to run the query "sp_help #tmp". Now you can see the collation value for #tmp table, it is different from our test database. Why it is different. 

Reason for different Collation:- When a database is created it automatically replicate the Server's collation in it. But we can change our database collation. After creating the test database I changed the collation.

We can change the database collation by right clicking on the database and select the option properties. In database property window, at the left side there is a menu Options, click on options, then you can see the collation label. You can change the database collation by selecting any collation from the drop down list.

Since #tmp tables are created in tempdb and tempdb collation is different from test database's collation that is the reason for getting the above error message. We are joining two tables on the bases of name column with different collation.

Solution for problem:-

Now I have dropped the #tmp table that I created earlier and created the new #tmp table with same columns, but this time I have overwritten the tempdb collation using collate keyword in front of the column name followed by the name of the collation that I want to use. I have put the same collation name that test database has. In this way we were able to resolve our problem.

I hope this article will give you a clear understanding about importance of collation in SQL Server.

You will see my next article very soon..till then take care.