SQL Server Collation

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties to data. A collation defines bit patterns that represent each character in metadata of database. SQL Server supports storing objects that have different collations in database. The options associated with collation are mentioned below :

• Case-sensitive (_CS)
• Accent-sensitive (_AS)
• Kana-sensitive (_KS)
• Width-sensitive (_WS)
• Variation-selector-sensitive (_VSS)a
• Binary (_BIN)
• Binary-code point (_BIN2)

Note : When Binary (_BIN) or Binary-code point (_BIN2) is chosen, other collation options aren’t available. To check server collation for an instance of SQL Server, use the below :

SELECT SERVERPROPERTY('collation');

To find list of collations that are available on instance of SQL Server, use the below :

SELECT * FROM sys.fn_helpcollations();

Collation levels : SQL Server supports following levels of collations :

1. Server-level collations : The default server collation is set during SQL Server setup, and it becomes default collation of system databases and user databases. After you’ve assigned collation to server, you’ll be able to change it only by exporting all database objects and data, rebuilding master database, and importing all database objects and data. Instead of changing default collation of an instance of SQL Server, you’ll be able to specify required collation once you create new database or database column. To find server-level collation for an SQL Server, use below query :

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

2. Database-level collations : The database collation is used for all metadata within database, and therefore collation is that default for all string columns, temporary objects, variable names, and other strings used in database. If no collation is defined while creating database, database will used default server collation. To create database with collation, one could use an CREATE DATABASE statement : 

Syntax –

CREATE DATABASE databasename COLLATE collationtype;

Example :

CREATE DATABASE GeekDB COLLATE Greek_CS_AI; 

To change collation of user database, one could use an ALTER DATABASE statement : 

Syntax –

ALTER DATABASE databasename COLLATE collationtype;

Note : Collation for system databases cannot be changed until change of collation for server. 

Example :

ALTER DATABASE GeekDB COLLATE SQL_Latin1_General_CP1_CI_AS; 

Note : Altering database-level collation doesn’t affect column-level or expression-level collations.

2. Column-level collations : While creating or altering table, one can specify collations for each character-string column by using COLLATE clause. If column collation is not specified, column is created with default collation of database. To change collation of column, we could use ALTER TABLE statement : 

Syntax –

ALTER TABLE tablename 
ALTER COLUMN columnname COLLATE collationtype;

Example :

ALTER TABLE Geektable 
ALTER COLUMN namecol NVARCHAR(10) COLLATE Greek_CS_AI; 

3. Expression-level collations Expression-level collations are used when statement is run, and they affect the way output is returned. This allows ORDER BY sort results to be specific. 

Syntax –

SELECT * FROM tablename 
ORDER BY columnname COLLATE collationtype;

Example :

SELECT * FROM Geektab 
ORDER BY columnname COLLATE SQL_Latin1_General_CP1_CI_AS;