SQL Server - Collation

> Database > MS SQL Server

1 - About

locale (Character set) in SQL Server

3 - Syntax

SQL_Latin1_General_CP1_CI_AS

where:

  • SQL_latin1_General = Collation Designator
    • The SQL is a prefix for old collations created prior the use of OS-level Collations
    • Defines the dictionary (therefore the sorting rules).
  • CP1 = Code Page 1252
  • CI = Case insensitive comparisons (a=A)
  • AS = accent sensitive, so 'é' does not equal 'e'
  • WS = width sensitive
  • KS = kanatype sensitive

See doc: Windows Collation Name structure

Advertising

4 - Management

4.1 - List

SELECT 
    name, 
    description,
    CONVERT(VARCHAR(100), COLLATIONPROPERTY(name, 'CodePage')) AS codePage, -- Convert the variant data type to varchar in case your client does not support it
    CONVERT(VARCHAR(100), COLLATIONPROPERTY(name, 'LCID')) AS LCID, -- See doc
    CONVERT(VARCHAR(100), COLLATIONPROPERTY(name, 'ComparisonStyle')) AS ComparisonStyle, -- Ignore case : 1, Ignore accent : 2, Ignore Kana : 65536, Ignore width : 131072
    CONVERT(VARCHAR(100), COLLATIONPROPERTY(name, 'Version')) AS Version -- version of the collation
    FROM fn_HelpCollations();

Doc:

4.1.1 - Windows Collations Support

SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE 'SQL%';  

4.2 - SELECT

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;    

5 - Example

  • SQL_Latin1_General_CP1_CI_AS: number first then letters:
  • ECBDIC characterset: COLLATE SQL_EBCDIC037_CP1_CS_AS letters first then number ?