Friday, September 28, 2018

sql server - Do Clustered Index and the table on which it is created both contain the actual data?




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?


Answer



I got the answer here:
https://www.youtube.com/watch?v=lYsVOB_A5o4




https://www.simple-talk.com/sql/performance/14-sql-server-indexing-questions-you-were-too-shy-to-ask/



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. Both are same thing.


No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...