Database Collation

It is possible to create database with different collation on the same instance of sql server with different collation.

Collation can be set at SQL Server instance Level, Database Level as well as Table Column Level. 

Let’s walk through with some examples.

I have Sqlserver 2008 R2 is running on my machine and which has default 

---Lets Create Databases with Canadian English and American English

--- check the data types by querying sys.types  table.

CREATE DATABASE EnglishCanada

COLLATE Latin1_General_CI_AS

GO

USE EnglishCanada

GO

SELECT *

FROM sys.types

GO

-- Create Case In-Sensitive Database

CREATE DATABASE EnglishUSA

COLLATE SQL_Latin1_General_CP1_CI_AS

GO

USE EnglishUSA

GO

SELECT *

FROM sys.types

GO

In the above diagram collation name Colum clearly showing Canada English and USA English. So it is possible to have different type of Collation database in the same instance.

Collation is code page to sort and compare the characters in the database.

Let’s check for Canada and USA English.

SELECT COLLATIONPROPERTY('Latin1_General_CI_AS','CODEPAGE')

GO

SELECT COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS','CODEPAGE')

Both are using same code page 1252 for sorting and comparing characters.

Now let’s compare characters For both Canada and USA English.

Run the following scripts and result for both says both are equal.

-- COMPARISON

USE EnglishUSA

GO

SELECT CASE

WHEN 'A' = 'a' THEN 'YES'

ELSE 'NO'

END AS USAENGLISH

USE EnglishCanada

GO

SELECT CASE

WHEN 'A' = 'a' THEN 'YES'

ELSE 'NO'

END AS CANADAENGLISH

Let’s do sorting test for both languages.

Run the following scripts and result for both has same value.

-- SORTING

USE EnglishUSA

GO

SELECT 'Aa' as example,1

UNION ALL

SELECT 'AA' as example,2

order by example desc

USE EnglishCanada

GO

SELECT 'Aa' as example,1

UNION ALL

SELECT 'AA' as example,2

order by example desc

With above test there is no absolute difference between Canada and usa English.

It’s clearly shown that you can have databases with different collation in a instance that is with different collation.

Collation can be set at Database level, Table level and column level.

Some useful commands.

--- Find the Collation at server level

select SERVERPROPERTY('Collation')

--- Find the collation at database level

GO

select DATABASEPROPERTYEX('testhari','Collation')

GO

--- Change the database collation.

USE testhari

GO

ALTER DATABASE testhari COLLATE Latin1_General_CI_AS

GO