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