Collations on Sql Server 2005+

This post is about collations in Sql Server 2005+.

I will give some simple information on:

  • What Collations are Available
  • What is the Server Collation
  • What is a Databases Collation
  • Altering Database Collation
  • Comparing Columns with different collations

What Collations are Available?

fn_helpcollations() function

SELECT * FROM fn_helpcollations()

Use this to view all available collations (name and description)

What is the Server Collation?

There are a few ways to do this!

sp_helpsort procedure

sp_helpsort

This will return the description of the collation that is default for the server.

SERVERPROPERTY function

SELECT SERVERPROPERTY('collation')

This will return just the name of the default server collation

What is a Databases Collation

SELECT DATABASEPROPERTYEX('MyTemporaryDatabase', 'Collation')
    AS DatabaseCollation;

This will return the collation of the specified database (“MyTemporaryDatabase”).

Altering Database Collation

ALTER DATABASE MyTemporaryDatabase
    COLLATE Latin1_General_CS_AI

Comparing Columns with Different Collations

To compare columns in a table with different collations you want to convert them to the same collation.

You can either specify the specific collation as follows:

SELECT  Data
FROM    MyDataTable
        JOIN MyOtherDataTable
WHERE   MyDataTable.Data COLLATE Latin1_General_CS_AI
        = MyOtherDataTable.Data COLLATE Latin1_General_CS_AI

Or you can specify that you want to convert the column(s) to the current databases default collation as follows:

SELECT  Data
FROM    MyDataTable
        JOIN MyOtherDataTable
WHERE   MyDataTable.Data COLLATE DATABASE_DEFAULT
        = MyOtherDataTable.Data COLLATE DATABASE_DEFAULT
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: