SQL SERVER – 2005 Collation Explanation and Translation

Just a day before one of our SQL SERVER 2005 needed Case-Sensitive Binary Collation. When we install SQL SERVER 2005 it gives options to select one of the many collation. I says in words like ‘Dictionary order, case-insensitive, uppercase preference’. I was confused for little while as I am used to read collation like ‘SQL_Latin1_General_Pref_Cp1_CI_AS_KI_WI’. I did some research and find following link which explains many of the SQL SERVER 2005 collation.

Complete documentation MSDN – SQL SERVER Collation

How to Read a SQL Server 2005 Collation Name

Once you know the pattern, collation names are easier to read than they look. A name like Latin1_General_CI_AS has a base part for the language rules, then a short list of flags:

• CI or CS: case insensitive or case sensitive.
• AI or AS: accent insensitive or accent sensitive.
• KS and WS: kana sensitive and width sensitive, when they appear.
• BIN or BIN2: binary sorting by code values, where BIN2 is the newer and more exact of the two.

Names that start with SQL_ are the older SQL Server collations, kept for compatibility. Names without that prefix are Windows collations. For new work a Windows collation is usually the better choice, unless you must match an older server.

To see what you have, run SELECT SERVERPROPERTY('Collation') for the server and DATABASEPROPERTYEX('YourDB', 'Collation') for a database. The function fn_helpcollations() lists every collation with a plain description.

Choose carefully at install time. Changing the server collation later means rebuilding the system databases, which is a lot of work. A mismatch between tempdb and a user database is a common source of the error Cannot resolve the collation conflict. When you compare a temp table column with a column in your database, add COLLATE DATABASE_DEFAULT to the temp table column to avoid it.

If you need a case sensitive comparison in only one query, you do not have to change the whole database. Add a COLLATE clause to that one comparison, for example WHERE LastName = 'Smith' COLLATE Latin1_General_CS_AS, and only that test becomes case sensitive. It can stop an index seek on that column, so check the speed as well.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.