Professional Microsoft® SQL Server® 2008 Programming

Chapter 6. Core Storage and Index Structure

Indexes. They may well be the second most important part (to tables) of your database planning and system maintenance. Why is it then that they are, all too often, an afterthought in many designs?

Think about it for a minute. Most database systems are based on the notion of fast and efficient data retrieval and maintenance. Indexes provide your database system with additional ways to look up data and take shortcuts to that data's physical location. The right index can cut huge percentages of time off your query executions. So, if efficient data retrieval and maintenance are why we build databases, and indexes are critical to the efficient access and maintenance of the data in databases, why is it that so many software architects move straight from determining a table layout to stored procedures or client code? Silly.

Now, don't get me wrong: thinking about stored procedures, client code, and other non-table elements is important, and most developers aren't going to leave a database with zero indexes. Indeed, at least a few indexes will show up in your database without you having to specify them. (Creating a primary key or unique constraint creates an implied index required to enforce those constraints.) It is, however, amazing just how often indexes are applied based on only a few minutes worth of guesses or purely to address a specific performance bug that showed up in QA (or worse, as a patch to a released product). In still other scenarios, ...