I've read in couple of articles that clustered index is as the same as table, or if you read using clustered index, you have the whole record.
Some say, clustered index stores the whole records in deepest leaf of index.
It is foggy for me, does clustered index has a copy of data?!!
for sure not, but I don't why people say that.
I read couple of articles but still it is not clear to me. Is clustered index is something virtual? Does it use B-Tree structure (like non-clustered index)?
Is there any schematic or model that describes how SQL Server stores/manages clustered index?
Best Answer
Yes this is correct. The leaf pages of the clustered index contain the actual table rows. This is the actual data. There is no other primary copy of it held elsewhere.
The leaf pages also allocate a few bytes to hold the address (file and page) of the next and previous pages in index key order to form a doubly linked list. Following the index in key order may or may not be the same as following it in physical order depending on the level of fragmentation.
The rows on the individual leaf pages may not be written physically exactly in index key order but the slot array on the page, with pointers to the rows it contains, is ordered by key order.
As long as the table doesn't fit on a single page then there will be one or more levels of the B-tree above the leaf. These higher levels contain index key values and pointers to pages in the level below to allow efficiently looking up key values. This is exactly the same principle used in nonclustered indexes too.
The leaf level pages of a clustered index are categorised as data pages and non-leaf pages as index pages. But the data pages are still part of the index.
(Image Source)
Also see my answer to a related question on Stack Overflow:
What do Clustered and Non clustered index actually mean?