Collation in SQL Server

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Types of collation sensitivity
  • Case Sensitivity
  • Accent Sensitivity
  • Kana Sensitivity
  • Width Sensitivity
Case Sensitivity
If A and a, B and b, etc. are treated in the same way, then it is case-insensitive.
A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.
  • CI specifies Case Insensitive 
  • CS specifies Case Sensitive
Accent Sensitivity
If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.
  • AI specifies Accent Insensitive 
  • AS specifies Accent Sensitive
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width Sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Query to check collation and compatibility level of a database
SELECT compatibility_level, collation_name
FROM   sys.databases
WHERE  name = 'DatabaseName'

Query to find out all available collations in SQL Server database
SELECT    [name]
        , COLLATIONPROPERTY([name], 'CodePage') AS [CodePage]
        , COLLATIONPROPERTY([name], 'LCID') AS [LCID]
        , COLLATIONPROPERTY([name], 'ComparisonStyle') AS [ComparisonStyle]
        , COLLATIONPROPERTY([name], 'Version') AS [Version]
        , [description]
FROM    fn_helpcollations()

In the next article, we will explore,
common issues encountered with collation

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Share this

Related Posts

Previous
Next Post »