I started reading about collations after I had a recent run in with them. As I read I started to realize that for a seemingly simple subject there was a lot to it. So I thought I would go over some of what I found out.

Where do we find collations? There are 3 different levels of collation settings.

Column

As I said in the previous post (Collation: Definition) the collation setting for the column determines how sorts and comparisons are handled. If no collation is specified when defining the column then it uses the database default. If you want to use a different collation then you use the COLLATE clause when defining the column. If you use the database_default keyword with the COLLATE clause then it will inherit the collation of the database.

Database

The database collation is the default for all new columns created within the database. It is also the default collation for all temporary objects, variables and in fact any string created within the database. Note that this does not affect temp tables which use the default collation of tempdb where they are stored. Also if you have a database with a case sensitive collation then you can create columns (or other objects such as variables) that differ only in the case of the characters. In other words you can create the columns “Name” and “name” in the same table only if the database has a case sensitive collation.

Note: You can not change the database collation settings for the system databases.

Instance

When you install an instance you are prompted for a collation setting. This particular setting determines the collation of the system databases. Because it sets the collation for the Model database this means it is the default setting for all new databases as well. You cannot use a Unicode only collation for the instance collation.

And while we are on the subject of location, you can get a list of all collations supported by SQL Server by querying the table valued function sys.fn_helpcollations().

Over the next few weeks I’m going to do a number of different posts around the surprisingly deep subject of collation.

As a start listen to Rob Farley’s hilarious talk on collation
Collation: Definition
Collation: Expression
Collation: Correction -> Expression, Column, Database
Collation: Correction -> Instance
Collation: Temporary objects & variables: Demonstration
Collation: Confusion