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?
Add a comment
|
2 Answers
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.
Comments
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.
2 Comments
Abe Miessler
On this particular database inserts/updates/deletions are only performed once per day in the middle of the night so I'm willing to take a hit on that performance. Knowing that it seems like a covering index would be the way to go then?
Andrew
Sounds more like a DW or Hybrid than a pure OLTP so a covering index is probably in your favour - but it really is a YMMV.