SQL Server - Collation

locale (Character set) in SQL Server

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

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

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

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;    

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