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.
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 is 65, 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 97 and áis 225. The ASCII value of o is 111 and ó is 243.
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.
SELECT s.name as 'Schema_Name', t.name as Table_Name, c.name AS Column_Name, c.collation_name AS Collation FROM sys.schemas s INNER JOIN sys.tables t ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON c.object_id = t.object_id WHERE collation_name is not null ORDER BY Column_Name
fn_helpcollations: Returns a list of all supported collations.
SELECT name, description FROM fn_helpcollations();
List all database with collation name
SELECT name, collation_name FROM sys.databases
Find Server level collation
select @@servername server_name,SERVERPROPERTY('Collation'), Collation
Comparison with different collation string column
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.Col1 COLLATE Latin1_General_CS_AS = TABLE2.Col1 COLLATE Latin1_General_CS_AS
