Time and time again I have run into the scenario where a developer who is moving into using databases is not solid on what an index is or how to correctly utilize them. While this is well-worn territory for the DBAs in the crowd, it bears taking some time for those who are not 100% on indexing concepts since the returns are so mammoth.

It is an absolutely HUGH slowdown anytime a computer has to go to a hard drive to read information. If we can provide a mechanism that allows the computer to skip reading data from disk then we save ourselves significant hourglass time. Leaning on my standard analogy, this is akin to the super-secret agent only having to look through 20-30 of the file boxes rather than the thousands of file boxes in the warehouse. One of the most common and most effective tool to avoid this disk time is the index.

An index is similar to the index in the back of a book in that it gives you a known place to quickly find references to the information you are seeking. Once you find your reference, you can go directly to the page(s) that has the information you are seeking, rather than reading the entire book. Similarly, when the super-secret agent enters the warehouse and uses the cross reference index books to go directly to the file they are looking for, they are utilizing an index.

Say you have a table with hundreds of thousands of entries. Creating an index using a unique key (think bank account number) will allow the computer to do just a little bit of disk reads to check the index, and then go directly to the record of interest.  I have, frequently, utilizing indexes in this way cut query time down from hours to a couple of seconds, literally!

If I have your attention at this point, then it’s time for you to do a little research. You’ll be very glad that you did. There is a lot of good information on the internet regarding the basics of indexes, and I’m not going to reinvent the wheel here. Do a few search queries on “SQL Server Create Index” and “SQL Server Indexing” for many good articles. In particular, I would suggest http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics.

This posting is part of a performance tuning series that will be discussing techniques that utilize indexes quite a bit, so be sure to read up if you’ve got any questions before going on.