SQL Server - Collation

About

collation in Sql server. locale (Character set) in SQL Server

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

Management

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:

• fn_helpCollation
• Collation Property doc

Windows Collations Support

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

SELECT

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;    

Example

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