Jan 21, 2014

Effect of collation in indexing in sql server

Changing the collation name of a string in sql server can decrease the performance of some queries. It is due to query cannot use some index to optimize the performance.  For example, suppose we have student table of following schema:

CREATE TABLE Employee(
    EmpId INT IDENTITY PRIMARY KEY CLUSTERED,
    EmpName VARCHAR(100),
    Country VARCHAR(50)
)

Inserting some data into it:

INSERT INTO Employee(EmpName,Country) VALUES('Scott','USA'),('Greg','usa'),('Marry','UK') 


Creating non clustered index on it

CREATE NONCLUSTERED INDEX NXI_Employee
ON Employee(Country)
INCLUDE(EmpName)

Now we will observe the execution plan of following sql query:

SELECT EmpName
FROM Employee
WHERE Country = 'USA'


It is using index seek to search the 'USA' in the country Column of Employee table.
Now we will check the execution plan of same query but different collation that is:

SELECT EmpName
FROM Employee
WHERE Country = 'USA' COLLATE Latin1_General_CS_AI


1 comment:

  1. Is there a fix on how to make the query use the index?

    ReplyDelete