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:

    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

ON Employee(Country)

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

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:

FROM Employee
WHERE Country = 'USA' COLLATE Latin1_General_CS_AI

1 comment:

Raju Angani said...

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