New record may be written twice in clusterd index structure

Posted by Cupidvogel on Stack Overflow See other posts from Stack Overflow or by Cupidvogel
Published on 2012-09-13T15:36:17Z Indexed on 2012/09/13 15:38 UTC
Read the original article Hit count: 255

As per the article at Microsoft, under the Test 1: INSERT Performance section, it is written that

For the table with the clustered index, only a single write operation is required since the leaf nodes of the clustered index are data pages (as explained in the section Clustered Indexes and Heaps), whereas for the table with the nonclustered index, two write operations are required—one for the entry into the index B-tree and another for the insert of the data itself.

I don't think that is necessarily true. Clustered Indexes are implemented through B+ tree structures, right? If you look at at this article, which gives a simple example of inserting into a B+ tree, we can see that when 8 is initially inserted, it is written only once, but then when 5 comes in, it is written to the root node as well (thus written twice, albeit not initially at the time of insertion). Also when 8 comes in next, it is written twice, once at the root and then at the leaf. So won't it be correct to say, that the number of rewrites in case of a clustered index is much less compared to a NIC structure (where it must occur every time), instead of saying that rewrite doesn't occur in CI at all?

© Stack Overflow or respective owner

Related posts about b-tree

Related posts about clustered-index