3.1 Introduction

There are many bells and whistles that can be tweaked to improve SQL Server performance. Some will provide a more positive benefit than others. However, to really improve performance, often with dramatic results, the database designer is well advised to concentrate his or her efforts in the area of indexing. The correct choice of index on a table with respect to the WHERE clause in a Transact-SQL statement such that the query optimizer chooses the most efficient strategy can have sensational results.

I was once asked to look at a query that performed a complex join and had not completed in over 12 hours. Who knows when the query would have completed had it not been cancelled by the user it may have still been running at the end of the year! Examination of the query showed that a join condition was missing in the WHERE clause as was an index on one of the large tables involved in the join. Making the appropriate changes meant that the query ran in less than 8 minutes!

This magnitude of performance improvement is not likely to be achieved every day but it makes an important point, namely that focusing effort in the area of indexing and query optimization is likely to produce good results for the effort involved and should be high on the database tuner's hit list.

So what are these indexes and why are they so important?

3.2 Data Retrieval with No Indexes

Imagine that this book had no...