Collation: Collation is set of rules which Determines how data are sorted and compared.
collations can be specified at many levels. When we install an instance of SQL Server ,we can specify the default collation for that instance. Each time we create a database,we can specify the default collation used for the database. If we do not specify a collation, the default collation for the database is the default collation for the instance.
- We can get list of Collation by Querying.
select * from ::fn_helpcollations()
- Employee_CS for (case sensitive collation i.e. SQL_Latin1_General_CP1_CS_AS )
- Employee_CI for (case insensitive collation i.e. SQL_Latin1_General_CP1_CI_AI )
--Creating table Employee_CS ,define collation at column level which is case sensitive and accent sensitive CREATE TABLE Employee_CS ( id INT IDENTITY(1, 1) , NAME NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS PRIMARY KEY )
--insert to value for name which is not same for case sensitive,both are different INSERT INTO dbo.Employee_CS ( NAME ) VALUES ( 'Bill'),('BILL') --Insertion will be completed without error even we have primary key on Name column cause of collation we define on it.
We got Successful insertion of the data in the table cause Bill and BILL is different while we are defining case sensitive collation type on Column.
- Let’s Create Another table Employee_CI and define collation at column level which is case sensitive.
--Creating table Employee_CI ,define collation at column level which is case insensitive ans accent insensitive CREATE TABLE Employee_CI ( id INT IDENTITY(1, 1) , NAME NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY )
Let’s insert Same name Bill and BILL into Name of Employee_CI.
--insert to value for name which is same for case Insensitive,both are different in term of case sensitive INSERT INTO dbo.Employee_CI ( NAME ) VALUES ( 'Bill'),('BILL') --Insertion steps throws error "Violation of PRIMARY KEY constraint"
- As you run insertion step it throws error cause as collation is case insensitive.
- Now Question is how we can compare column that having different collation?
- Let’s insert Value in Table Employee_CI so we can compare Column Name with table Employee_CS
INSERT INTO dbo.Employee_CI ( NAME ) VALUES ( 'Bill'),('BILLS')
- When we write simple joining statement it throws error that collation conflict occur.
SELECT * FROM dbo.Employee_CI EI INNER JOIN dbo.Employee_CS ES ON EI.NAME = ES.NAME --Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.
- We have to provide collation name where we are joining table based on column comparison as given below.
--You can give Default collation by stating DATABASE_DEFAULT.but you first got to know what collation current database have. SELECT * FROM dbo.Employee_CI EI INNER JOIN dbo.Employee_CS ES ON EI.NAME COLLATE SQL_Latin1_General_CP1_CI_AI= ES.NAME COLLATE SQL_Latin1_General_CP1_CI_AI
- Here is code by which we can get collation at database level and server level.
SELECT DATABASEPROPERTYEX('sandbox','collation')--Gives Database level collation SELECT SERVERPROPERTY('collation')--Gives Server Level collation