SQL Server / Azure SQL / TSQL Collation

SQLAzureSQL Server

Please contribute by voting. Thanks!
2
A few notes about collation in SQL Server & TSQL. Collation in a database controls stuff like ordering and comparison of strings. Selecting the wrong collation can, for instance, make names be listed in the wrong order - this can be very frustrating for an end user.

Collation in SQL Server


Each instance of SQL Server has a default collation. You retrieve it by running the following command:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

Changing collation on instance level can be done, but it's kind of tricky so be sure to pick a collation with care when creating the instance.

Each database created on the instance will get the instances’ default collation, unless specified. You can list each database and their collation with the following command:

SELECT name, collation_name FROM sys.databases;

You can specify the collation when creating a database as so:

CREATE DATABASE MyDatabase COLLATE Finnish_Swedish_CI_AS;

You can list all supported collations by running the following command:

SELECT name, description FROM sys.fn_helpcollations();

Collations can be overridden at column level as well, use the following command to view the collation for a specific column in a specific database:

USE DATABASENAME;
SELECT name, collation_name FROM sys.columns WHERE name = N'COLUMNNAME';

You can change collation on database and column levels by using the ALTER syntax, e.g.:

ALTER DATABASE MyDatabase COLLATE Finnish_Swedish_CI_AS;
ALTER TABLE UserAccounts ALTER COLUMN Name NVARCHAR(50) COLLATE Finnish_Swedish_CI_AS NOT NULL;

Collation in Azure SQL


The default collation for Azure SQL is SQL_Latin1_General_CP1_CI_AS and is an English (US) collation. Even if it says Latin1 it can still hold characters from the Unicode-code page.

As far as I know, the default collation cannot be changed in the current version of Azure SQL. This means that you have to be sure to specify it when creating your databases!

Another important note is that it can be hard to change the collation on database level in Azure SQL. It should be possible to do so, but I have never succeeded. Changing the collation on database-level will most likely result in the following error:

Msg 42008, Level 16, State 5, Line 1
ODBC error: State: 42000: Error: 5030 Message:'[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The database could not be exclusively locked to perform the operation.'.
Msg 5069, Level 16, State 3, Line 1
ALTER DATABASE statement failed.

Article created: Apr 26 '16. Edited Apr 26 '16.

Your comment

You need to sign up / log in to comment this article

Author

Created by Valentin Grigoras [3] Apr 26 '16

Share article

Do you know about

ReSharper?

Write an article