CODEWITHSUNDEEP
sql-server
NikRED
NikRED

Reputation: 1195

How is Clustered and Column store index storage

I have few questions about clustered index and column store index.We know in clustered index the physical order of data for particular column is changing and storing in the leaf node of the binary tree.So,my question are:
1)If we create clustered index on columnA,the data of that columnA will be removed from the actual strage place and added to a binary trees leaf? or it is just rearranging the data in the original storage place?

2)What about column store index,here also the data from specific column will be removed from actual storage location and loaded in to another separate file segment?

3)From the above 2 question,If the data is moving from original location,for eg: we have a tableA having coulmns colA,colB and if we create any of the above 2 indexes on colA, then original location contains only colB data? and colA is in some other location?

Upvotes: 0

Views: 1986

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294437

1) When you create a clustered index the table is rebuild as a clustered index. There is no 'original storage', the clustered index is the table and the table is the clustered index. A clustered index is organized as a B-tree structure (not to be confused with a binary tree).

2) A columnstore non-clustered index is a secondary index on a table. As with any secondary (non-clustered) indexes every row in the non-clustered index is a copy of the data from the table base heap or clustered index. A columnstore index is not a B-tree, nor a heap, but a new type of data organization optimized for column oriented storage. To understand how column oriented storage works in general, read the C-Store paper.

3) No. Adding non-clustered indexes of any nature, including special indexes like XML, spatial and columnstore, never remove data from the table base heap or clustered index. Non-clustered indexes always contain a copy of the data. The addition of a non-clustered columnstore index does not change in any way rows and columns in the table base heap or clustered index (I suspect what you call the 'actual storage').

Upvotes: 2

Related Questions