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.

  3. 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 columnnmae COLLATE collationtype;

    Example :

    ALTER TABLE Geektable 
    ALTER COLUMN namecol NVARCHAR(10) COLLATE Greek_CS_AI; 
  4. 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 nname COLLATE SQL_Latin1_General_CP1_CI_AS; 
    My Personal Notes arrow_drop_up

    Check out this Author's contributed articles.

    If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

    Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


    Article Tags :
    Practice Tags :


    Be the First to upvote.


    Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.