Monday, June 10, 2013

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 aB 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 Ao 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

0 comments:

Post a Comment