Professional SQL Server™ 2005 Programming

8.2. Understanding Indexes

Webster's dictionary defines an index as:

A list (as of bibliographical information or citations to a body of literature) arranged usually in alphabetical order of some specified datum (as author, subject, or keyword).

I'll take a simpler approach in the context of databases and say it's a way of potentially getting to data a heck of a lot quicker. Still, the Webster's definition isn't too bad — even for your specific purposes.

Perhaps the key thing to point out in the Webster's definition is the word usually that's in there. The definition of "alphabetical order" changes depending on a number of rules. For example, inSQL Server, we have a number of different collation options available to us. Among these options are:

• Binary — Sorts by the numeric representation of the character (for example, in ASCII, a space isrepresented by the number 32, the letter D is 68, but the letter d is 100). Because everything is numeric, this is the fastest option — unfortunately, it's also not at all the way in which people think, and can also really wreak havoc with comparisons in your WHERE clause.

• Dictionary order — This sorts things just as you would expect to see in a dictionary, with a twist — you can set a number of different additional options to determine sensitivity to case, accent, and character set.

It's fairly easy to understand that, if we tell SQL Server to pay attention to case, then A is not going to be equal to a. Likewise, if we tell it to be case insensitive, ...