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

Now it is not using index seek to search the 'USA' in the country Column of Employee table. Now it will follow index scan (slow process)

Indexes best practices in sql server: Where to start creating indexes?
Sql server query optimization tips : Tuning best practices with examples
Fastest or most efficient way to insert data in sql server
Index in sql server with examples
What is clustered index in sql server
What is non clustered index in sql server
When sql server is able to use indexes and when not