Types of collation sensitivity
- Case Sensitivity
- Accent Sensitivity
- Kana Sensitivity
- Width 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
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
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
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