CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Zohaib Hassan
Zohaib Hassan

Reputation: 51

What is difference between non-clustered index and non-clustered ColumnStore index

I try both indexes in SQL Server 2012 but confused with syntax, is there any difference between both type of index or both are same? If same than why they create new keyword columnstore in sql. Thank you in advance.

Upvotes: 1

Views: 1225

Answers (1)

Vijayanath Viswanathan
Vijayanath Viswanathan

Reputation: 8541

As shown in the simplified picture below, both indexes are organized as columns but NCCI is created on an existing rowstore table as shown on the right side in the picture below while a table with CCI does not have a rowstore table. Both tables can have one or more btree nonclustered indexes.

enter image description here

Other than this, the physical structures on how data is stored in delta and compressed rowgroups enter image description hereare identical and both kinds of indexes have same performance optimizations including batchmode operators. However, there some key differences between these and the table below lists the main differences.

Source :- https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/07/18/columnstore-index-differences-between-clusterednonclustered-columnstore-index/

Upvotes: 2

Related Questions