Professional SQL Server™ 2005 Programming

23.2. Index Choices

Again, this is something that was covered in extreme depth previously, but the topic still deserves something more than a mention here because of its sheer importance to query performance.

People tend to go to extremes with indexes — I'm encouraging you not to follow any one rule but to instead think about the full range of items that your index choices impact.

Any table that has a primary key (and with very rare exception, all tables should have a primary key) has at least one index. This doesn't mean, however, that it is a very useful index from a performance perspective. Indexes should be considered for any column that you're going to be frequently using as a target in a WHERE or JOIN, and, to a lesser extent, an ORDER BY clause.

Remember though, that the more indexes you have, the slower that your inserts are going to be. When you insert a record, one or more entries (depending on what's going on in the non-leaf levels of the B-Tree) have to be made for that index. That means more indexes and also more for SQL Server to do on inserts. In an Online Transaction Processing (OLTP) environment (where you tend to have a lot of inserts, updates, and deletes), this can be a killer. In an Online Analytical Processing (OLAP) environment, this is probably no big deal since your OLAP data is usually relatively stable (few inserts), and what inserts are made are usually done through a highly repetitive batch process (doesn't have quite the lack of predictability that ...