Tuesday, 27 September 2011

Collation in SQL Server

What is collation
In one line, collation is set of rules that governs how to use characters, alphabets or language in SQL Server.
We came across problem of "Cannot resolve collation conflict" whenever we try to fetch data from two databases having different collation.Here i am elaborating the issue with detail by taking example.
Example
Let's start from the scratch........
Step 1 : we are going to create two database with different collation.
create database DB1 collate Latin1_General_CI_AS
Create database DB2 collate Latin1_General_CI_AI
Step 2 Creating tables and insertion of data.
use DB1
Create table Test1(id1 int not null primary key, Name1 varchar(255))
Insert into Test1 (id1,Name1) values (1,'Amit')
use DB2
Create table Test1(id int not null primary key, Name varchar(255))
Insert into Test1 (id,Name) values (1,'Amit')
Step 3 : Let's fire two query as below :
Query 1 : Joining based on ID (Int datatype)
select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.id1=T2.id
we will get the result as below :
Id1    Name1   Id   Name
1       Amit       1     Amit
** that is what we want......
Query 2 : Joining based on Name (varchar datatype) :collation will play role here
select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name
we will get error in this case as below..
Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_CI_AS" in the equal to operation.
Question
Why we are getting error in second case, though we are able to execute the first query.
Answer
This happens because of different collation of database, difference in database collation won't effect  int datatype, hence generating result in first case while throws error when we try to fetch data based on joining of Name (varchar datatype) column
Solution
Just a minor updation in query will do our work, i.e set a collation to the query no 2 either of database DB1 or DB2, have a look on collate part in below query
we can do either way
1. Select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name
    collate Latin1_General_CI_AS
2. Select * from DB1.dbo.Test1 T1 join DB2.dbo.Test1 T2 on T1.Name1=T2.Name
    collate Latin1_General_CI_AI
Both will yield result as desire, in our case we will get result as follow
Id1    Name1   Id   Name
1      Amit       1    Amit
Author : We will discuss on some other topic in next post.

Triggers


Firing of Triggers in Order
  • Some time we need triggers to fire in order, in general we have no specific control over the order in which triggers will fire, in order to control the same, i eventually come across to the system stored procedure named sp_settriggerorder.
  • sp_settriggerorder.is meant for to provide ordering to the triggers to fire, parameters are as below
           sp_settriggerorder_parameters


           Exec sp_settriggerorder @triggername = trigger_name
          @order =[FIRST|LAST|NONE],
          @stmttype =[INSERT|UPDATE|DELETE|],
          @namespace = [DATABASE|SERVER|NULL]



Example
  • Scenario : We have table say Table1 where we want to insert data, we need to fire two insert triggers on Table1, also we need to fix the order in which these triggers will fire.
  • Workaround : First of all, we have created Trigger1 and then Trigger2, we have inserted the record into the table
Sequence of execution are as follow :
  1. INSERT command
  2. Trigger1
  3. Trigger2
Note : if we drop the trigger1 and recreated the same, order of the execution of triggers has   been change as below, as system prioritize the triggers as First come first server basis.
Sequence of execution will be as follow :
  1. INSERT command
  2. Trigger2
  3. Trigger1
Here comes the role of sp_settriggerorder, we need to do some tweaking while providing parameters in sp_settriggerorder as below
exec sp_settriggerorder @triggername = Trigger1
@order = FIRST,
@stmttype =INSERT,
@namespace = NULL
exec sp_settriggerorder @triggername = Trigger2
@order = LAST,
@stmttype =INSERT,
@namespace = NULL
Now if we executed the insert command it will always executed in below mention order.:
Sequence of execution are as follow :
  1. insert command
  2. Trigger1
  3. Trigger2
Note : it doesn't matter whether trigger1 is created first or last.
Over All order of execution will be as follow
exec sp_settriggerorder @triggername = Trigger1
@order = FIRST,
@stmttype =INSERT,
@namespace = NULL
exec sp_settriggerorder @triggername = Trigger2
@order = LAST,
@stmttype =INSERT,
@namespace = NULL
Insert into table1 (col) values(value1)
         Result : 
Message
Firing modified Trigger1
Firing Modified Trigger2
Author : We will discuss on some other topic in next post.