Collations in SQL Server

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data you will be working with.

When you select a collation for your server, database, column, or expression, you are assigning certain characteristics to your data that will affect the results of many operations in your database. For example, when you construct a query by using ORDER BY, the sort order of your result set might be dependent on the collation that is applied to the database or dictated in a COLLATE clause at the expression level of the query.

A collation can contain any or all of the following characteristics:

• Case sensitivity
• Accent sensitivity
• Kana sensitivity
• Width sensitivity

To best use collation support in SQL Server, you must understand the terms that are defined in this topic, and how they relate to the characteristics of your data.

Case sensitivity

A and a, B and b, etc. Example;

PRINT 'Testing Case Sensitivity......'

IF (SELECT N'A' COLLATE Latin1_General_CS_AS)  = (SELECT 'a' COLLATE Latin1_General_CS_AS) 

     PRINT 'They are equal'

ELSE 

     PRINT 'They are NOT equal'

PRINT 'Testing Case Insensitivity......'

IF (SELECT N'A' COLLATE Latin1_General_CI_AS)  = (SELECT 'a' COLLATE Latin1_General_CI_AS) 

     PRINT 'They are equal'

ELSE 

     PRINT 'They are NOT equal'

Accent sensitivity

a and á, o and ó, etc. Example;

PRINT 'Testing Accent Sensitivity......'

IF (SELECT N'A' COLLATE Latin1_General_CI_AS)  = (SELECT 'á' COLLATE Latin1_General_CI_AS) 

     PRINT 'They are equal'

ELSE 

     PRINT 'They are NOT equal'

PRINT 'Testing Accent Insensitivity......'

IF (SELECT N'A' COLLATE Latin1_General_CI_AI)  = (SELECT 'Á' COLLATE Latin1_General_CI_AI) 

     PRINT 'They are equal'

ELSE 

     PRINT 'They are NOT equal'

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity

A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive