What is Collation and is it that Important ?

Collation is defined as set of rules which determines how data comparison and data sorting would take place. Character data is sorted using correct character sequence and with options for specifying case and accent sensitivity.

Example:
For case sensitive rule, character A and a are different as it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97.

For accent sensitive rule, character "ä” and "a" are different as it uses ASCII code to differentiate the input. The ASCII value of a is 97 and ä is 225.

Levels:
First Level , specified at instance level during SQL Installation.

Second level , when we create a database, one can specify the default collation used for the database. If you don’t specify a collation, database is created with the default collation of the instance.

Third level, when we define a character column, you can specify its collation. If you do not specify a collation, the column is created with the default collation of the database.

We cannot specify collation for character variables and are always created with the default collation of the database.

General Recommendation:
System Databases collation cannot be changed directly and would require rebuild or reinstall of SQL Server system databases which would need downtime.

Database or column Collation could be changed at any point of time without downtime , but best practice is to keep user database in same collation as System Databases.

Name (required)

Email Address (required, will not be published)

Website Address

<%= commentBody %>