Does Covering Index Duplicate Data?

Question

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)?

Answer 1

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.

Answer 2

Clustered index is a part of the table, so it's just updating the table itself. If it was non-clustered index then the answer would have been yes.

CLUSTERED Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.

//edit: I see I've understood it another way round. The point was that if you update a column you have to update: 1) clustered index 2) all non-clustered indexes containing that column

There's always a question when designing db what/how many indexes to create - it's a case of balance between reading and writing speed (and what's really needed).