Tuesday, January 26, 2016

Finding Collation used by the SQL Server objects

Collation is locale specific and you choose while installing the SQL Server. Sorting depends on this as well as case sensitiveness.


Collation.jpg


You can also search to find what kind of collation is supported on your server using the following query for the whole database (this example is for the master database)
Select name, COLLATION_NAME from sys.databases where name=N'master';
Response: SQL_Latin1_General_CP1_CI_AS master
If you have a column that is of varchar data type (something textual) you use this query for the column,
SELECT name, collation_name FROM sys.columns WHERE name = N'optname';
Response: optname SQL_Latin1_General_CP1_CI_AS

Note that there is a column called optname in the master database
You can find all the collations that are supported using this query:


CollationsSQLServer2012.jpg