SQL Server Collations and Index Operations

I don't deal with SQL Server collation issues very often. I suppose that is probably the case with most SQL Server database administrators in the U.S. -- the default collation for systems using US English (en-US) is SQL_Latin1_General_CP1_CI_AS, which is case insensitive. When I ran into a collation issue recently, it was (not surprisingly) one I hadn't dealt with before. Here is a query and execution plan that had been running in production:

(@UserName VARCHAR(50))
SELECT Yelfan, UserName, Salier, Mahemi
FROM dbo.Rectaph
WHERE UserName = @UserName

As you can probably tell from the query execution plan, there is an index seek operation in use. Even with 1.2 million rows in table dbo.Rectaph, the query is extremely efficient. Later on, the development team realized they needed the search query to be case sensitive. To accomplish this, the query was modified as follows:

(@UserName VARCHAR(50))
SELECT Yelfan, UserName, Salier, Mahemi
FROM dbo.Rectaph
WHERE UserName = @UserName COLLATE Latin1_General_CS_AS

But this changed the query execution plan: instead of an index seek, we now get a much less efficient index scan.

There is a CONVERT_IMPLICIT operation in the WHERE clause predicate, so the index scan is no big surprise here.

My performance challenge was to coerce an index seek while still honoring case sensitivity. Here's the revised query I used that achieved the goal:

(@UserName VARCHAR(50))
SELECT Yelfan, UserName, Salier, Mahemi
FROM dbo.Rectaph
WHERE UserName = @UserName
AND UserName = @UserName COLLATE Latin1_General_CS_AS

I ended up combining the WHERE clause predicate from the first query with the WHERE clause predicate from the second query. The first predicate is not case sensitive, but the second predicate is case sensitive. The first predicate gives us an index seek without CONVERT_IMPLICIT operations.

There is, however, a CONVERT_IMPLICIT operation for the second predicate.

But performance is very nearly as good as it was with the first query. Huzzah!