Menu
Clustered indexes sort and store the data rows in the table or view based on their key values.
https://msdn.microsoft.com/en-IN/library/ms190457.aspx
It means if a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index.
Secondly, the leaf nodes of a Clustered Index contains data pages of the table on which it is created.
https://technet.microsoft.com/en-us/library/ms177443%28v=sql.105%29.aspx
So, my question is that same data is stored in the Clustered Index and table on which it is created?
If Yes, then what is the need to sort the table in the same order as clustered index? Because clustered index itself stores the row-level data in it’s leaf nodes.
And if NO, then what is the actual theory behind the creation of Clustered Index?
501
A clustered index is not a separate index from table, it is the table. The clustered index is not a separate entity like it is with other index types, but rather a mechanism for sorting the table and facilitating quick data access. So, we can either say data is stored in clustered index or data is stored in table.
If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
In a clustered index, when rows are inserted, updated, or deleted, the underlying order of data is retained. The rows of data are only stored in the leaf nodes at the lowest level of the index. These are the data pages that contain all the columns in the table, ordered by the clustered index columns.
There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.
The data is stored in the leaf level of the clustered index - that IS the table data!
It's not stored in the table and in the clustered index - the clustered index's leaf level IS the table data.
119
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
