• shian_lineage2 (2/22/2013)


    It's a good idea, but why not use included fields, which will reduce the overhead further?

    I'm wondering - why do you say that included columns would reduce overhead? I assume you mean the overhead of maintaining the index when there are inserts, updates, or deletes. If so, included columns actually increase the overhead because the data for the included columns is stored at the leaf level of the nonclustered index and so must be managed whenever the data in the underlying table changes. Included columns are a good idea when the increased performance of "covered" queries that use the index outweighs the costs of maintaining the index.

    At any rate, the author here described a filtered index that included ALL the columns of the table. He explained pretty well that the costs of maintaining such an index were small enough and easily justified by the performance gain of queries that could use that index.

    Jason Wolfkill