SQL Server Collation

Q. Can instance level collation be different from database collation?

A. Yes , they can be different. Database collation can be changed but server collation can’t be changed without reinstallation.

========

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.

A table and column can have different collation than database and server

Create table Table1 ([name] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[name2] [char] (18) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
[name3] [char] (15) COLLATE )

 Create database Db1 collate Latin1_General_CS_AS_KS_WS

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 is65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97and áis 225. The ASCII value of o is 111 and ó is 243.