Execution plan: Nonclustered index update= Clustered index update ???

Question

Execution plan: Nonclustered index update= Clustered index update ???

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

For all practical purposes, the clustered index IS the table. If you update anything in the table, it has to update the clustered index.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

lnoland SSCommitted Points: 1783 More actions · Answer 2

GSquared (1/13/2011)
For all practical purposes, the clustered index IS the table. If you update anything in the table, it has to update the clustered index.

I thought, perhaps, that was it. It just seems somewhat unintuitive to me -- for me anyway, logically, the table is distinct from the index even if it is physically implemented as part of the index.

Thanks.

- Les

GSquared SSC Guru Points: 260824 More actions · Answer 3

lnoland (1/13/2011)
GSquared (1/13/2011)
For all practical purposes, the clustered index IS the table. If you update anything in the table, it has to update the clustered index.
I thought, perhaps, that was it. It just seems somewhat unintuitive to me -- for me anyway, logically, the table is distinct from the index even if it is physically implemented as part of the index.
Thanks.
- Les

The table is just a set of definitions for columns and the data in them. The data in the table is either stored in a heap, or in a clustered index. The clustered index includes all the data in the table and defines the physical storage of the data, except in heaps of course.

Hence, any change in data in a table with a clustered index is done in the clustered index. It could be called a "table update" instead of a "clustered index update", but it would still be the same thing, would still be necessary, and would still show up in your execution plan. Calling it a "clustered index update" makes it clear that it's not a "heap update", and vice versa, where "table update" wouldn't tell you which one it was, and would thus be less useful data for performance tuning.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon