SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Collation: Location

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.


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.


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.


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

Filed under: Collation, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, collation, language sql, microsoft sql server, T-SQL


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...