If you don't have to deal with case sensitivity, count yourself lucky! SQL
2000 is much better than SQL 7 with regard for collations, supporting collations
at database, table, and column level. In this article I'm going to delve into a
interesting (or obvious?) item I noticed recently when I did some performance
Basically we were doing a join to a table where we had to treat one column as
case sensitive. The table in question had about 130k rows and had an index (not
unique) on the column we were joining to. Not a huge table, a join to it should
be reasonably fast, yet I saw a high number of reads. Digging into the code -
which I sadly admit to writing - I found that we were handling the issue of case
sensitivity by converting the column to varbinary, a technique that works in
SQL7 or SQL 2000. An example written using a different table and simplified
syntax is shown here:
select * from instantforum_members where convert(varbinary(50), fullname) = 'andy warren'
If you're using SQL2K, you can accomplish the same thing or even better
depending on your needs by using a collation instead, like this:
select * from instantforum_members where fullname='andy warren' collate Latin1_General_CS_AI
Before we go on, let's start with what a simpler non case sensitive query
would look like and do:
select * from instantforum_members where fullname='andy warren'
You can see that just doing a simple select for my name results in a plan
that starts with an index seek, which is good, and has a cost of .003283. The
cost is relative, but in practical terms this is a very fast efficient query.
Now let's try the method I was using to handle sensitivity:
Here you can see the percentages shift as it does an index scan rather than
an index seek, no doubt caused by the convert statement which requires it to
touch every row. What's far more interesting is that the query cost jumps to
.508702, a huge jump! I ran the statement that used collate, not really
expecting a difference but you have to try, resulting in the exact same query
plan. That seems to make sense, it has to do the same work. Being curious, I
tried one more query specifying the collation as case insensitive and wound up
with the same results. Double checking the database found that I wasn't
specifying the correct & current collation which was
SQL_Latin1_General_CP1_CI_AS. Running one more time resulted in the same results
that I got in the first query with no collation specified.
Sorry if that is confusing. The net is that if you specify any collation
except the one in use on that column or you use a convert you're going to force
SQL to check each row, most likely resulting in either an index scan or a table
That got me thinking about how I could improve the performance without
changing the collation (an article for another day!). My thought process was
that the query itself was quick, the case sensitive compare slow only because it
had to look at every row, how to limit the query first and then do the compare.
For once it turned out to be exactly that easy, resulting in the following:
select * from instantforum_members where fullname = 'andy warren' and convert(varbinary(50), fullname) = 'andy warren'
Just that simple change gave me the results I needed at basically zero cost.
I've only tested on two tables so I can't say with certainty that the results I
show here are always true, but so far it seems to be consistent. It also shows
how even the simplest change can have an impact on query efficiency. In a follow
up article I'll look into others options including making the column case