Search code examples
sql-server-2008covering-index

Does Covering Index Duplicate Data?

Suppose we have this index

CREATE INDEX IX_test ON t1(c1) INCLUDE (c2)

Does this mean that we will have c2 in both index page and the actual data page? The real question is - does updating c2 mean that SQL Server will have to update IX_test and the actual data row (clustered index)?

Solution

  • Yes, just like including any field in an index duplicates data. And all indexes (indices) that include a field have to be updated when the field changes.

    So when you extend an index to cover a query (or more than 1), you are duplicating data. It's always a trade-of.