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
