SQL Server index question

Question

When creating an index for a specific query is it good practice to index all columns that are in the where clause and then have any columns selected in the "included columns" section? Should I leave bit columns out of the index?

Answer 1

It always depends. There are many factors at play: index size, columns selectivity, query frequency, updates rate etc. There is no generic rule to leve bit column add or to add them in, the answer is always specific to a particular question.

• Index Design Basics
• General Index Design Guidelines
• Clustered Index Design Guidelines
• Nonclustered Index Design Guidelines

Answer 2

Bit of a classic "it depends". If you include them, then you are creating a covering index, which will be far more efficient but equally you will incur a penalty on row insertion, deletions and some updates.

You also pay a price on the disk space, so if you are going to include very wide columns on a large table, you are balancing the increase in storage overhead vs the performance gain.

All things being equal, a covering index is normally a good thing, but there are caveats such as the space and overhead on other operations to be mindful of.