Collation in SQL Server

In this article, I have explain you about the Collation in SQL Server, Default Server Collation, Databases have a different collation to the server default, Collation for each column in my database, Change Collation of a database or a column.

We all know SQL server is Case InSensitive. Do you know Why?

It is just because of our Default Collation SQL_Latin1_General_CP1_CI_AS
Latin1_General represents U.S. English
CP1 specifies code page 1252
CI- Case InSensitive
AS- Accent Sensitive

What is Collation?

Collation defines how data is sorted and compared. Character data can be sorted and compared by case sensitive, accent marks and character width.
Case sensitivity
Uppercase and lowercase characters considered unequal
A <> a
Accent sensitivity
Accented and unaccented characters considered unequal
'e' <> 'ë'
Kana Sensitivity
Japan has 2 types of characters Hiragana and Katakana. Kana sensitivity collation treated them as unequal.
Width sensitivity
A single-byte character when represented as a double-byte character it is unequal.

How to see what collation is SQL installed under?

SELECT SERVERPROPERTY('COLLATION')

What are the collations available in SQL Server?

SELECT Name, Description FROM fn_helpcollations()

What are the collations for each database?

select Name as DatabaseName,DATABASEPROPERTYEX(name,'COLLATION') DBCollation 
from sys.databases

Which databases have a different collation to the server default?

select Name as DatabaseName,DATABASEPROPERTYEX(name,'COLLATION') DBCollation 
from sys.databases
where DATABASEPROPERTYEX(name,'COLLATION') <> SERVERPROPERTY('COLLATION')

How to check collations for each column?

SELECT name, collation_name FROM sys.columns

How to Change Collation of a database?

ALTER DATABASE DataBaseName COLLATE Latin1_General_CS_AS

How to make SQL Server Case Sensitive?

To make the query case sensitive, the collation of the query needs to be changed.


Create table CollateDetail (TestName varchar(20))
Insert into CollateDetail values ('Case Check'),('CASE CHECK'),('case check')

Query
select * from CollateDetail where TestName= 'Case Check'


TestName
Case Check
CASE CHECK
case check

By changing the collation type we can make our query case sensitive

ALTER TABLE CollateDetail ALTER COLUMN TestName VARCHAR(20) 
COLLATE Latin1_General_CS_AS

Query
select * from CollateDetail where TestName= 'Case Check'


TestName
Case Check