CODEWITHSUNDEEP
sqlsql-serverdatabaseindexing
Chad
Chad

Reputation: 133

Difference between non-clustered row based index and column- store index

What is difference between non-cluster row based index and column- store index? For example , there is a table with these columns:

ID, Name, family, Description

Index creation scripts:

CREATE NONCLUSTERED INDEX IX_Name   
ON TestTable (Name);   


CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Name_CS 
ON TestTable (Name);

I think in index page two above indexes have same structure? So what is difference between two above indexes?

Upvotes: 1

Views: 311

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Row-store and column-store indexes are quite different. Have you reviewed the documentation?

However, for a single column, the differences for non-clustered indexes are smaller. One fundamental difference is that the column-store automatically compresses the data. This can significantly reduce the size of the index.

On the other hand, column-store indexes are not generally used for index seek operations. Generally the entire column is scanned. Because the column in the column store index is much smaller in the row, this is much faster than a full table scan. It is also generally faster than a scan on the row-store index with one column.

Which index to use depends on the types of queries you want to run. I suspect that for a single column, the row-store is more appropriate in more situations.

Upvotes: 1

Related Questions