October 28, 2014 at 11:19 pm
Hello Experts,
The columnstore clustered index doesn't allows any other index to be built on that table and that makes sense as we are storing the complete table in columnar storage and hence we don't need any other index on that.
But why a nonclustered columnstore index doesn't allows other nonclustered columnstore index on the same table.
Also why Clustered columnstore index is updateable in 2014 but still the nonclustered columnstore index in not updateable.
I need some logic which is internals to index operations in 2014.
October 29, 2014 at 3:20 am
er.mayankshukla (10/28/2014)
Hello Experts,The columnstore clustered index doesn't allows any other index to be built on that table and that makes sense as we are storing the complete table in columnar storage and hence we don't need any other index on that.
But why a nonclustered columnstore index doesn't allows other nonclustered columnstore index on the same table.
Also why Clustered columnstore index is updateable in 2014 but still the nonclustered columnstore index in not updateable.
I need some logic which is internals to index operations in 2014.
When you create a columnstore index, you're creating a different data storage mechanism. It's pivoting and aggregating the storage. The older technology, the nonclustered columnstore, was designed to work on heaps and standard clustered indexes in combination with existing indexes. But, functionally, it's similar to the standard clustered index, you only get one per table. It's a limitation driven by how the engine implemented the columnstore index. That's also why they're not updateable. It's a limitation in place in order to support the hybrid approach of having the colunstore index in combination with the other standard structures. When they released the clustered columnstore index, which is a completely new storage mechanism, it removed some of the limitations of the nonclustered columnstore. But, just as with the standard clustered index and the nonclustered columnstore index, you only get one per table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2014 at 3:50 am
The older technology, the nonclustered columnstore, was designed to work on heaps and standard clustered indexes in combination with existing indexes. But, functionally, it's similar to the standard clustered index, you only get one per table.
Thanks Grant,
Clustered columnstore will store all the columns in a columnar storage, however the nonclustered will store only the key column in columnar storage
So, How can we say that nonclustered is functionally same as clustered.
May be I am missing some insight to these columnstore concept.
October 29, 2014 at 4:16 am
er.mayankshukla (10/29/2014)
The older technology, the nonclustered columnstore, was designed to work on heaps and standard clustered indexes in combination with existing indexes. But, functionally, it's similar to the standard clustered index, you only get one per table.
Thanks Grant,
Clustered columnstore will store all the columns in a columnar storage, however the nonclustered will store only the key column in columnar storage
So, How can we say that nonclustered is functionally same as clustered.
May be I am missing some insight to these columnstore concept.
I didn't say they were the same because they're not, exactly. But, for the columns stored in the nonclustered columnstore, the behavior is pretty much identical to the columns stored in the clustered column store. Remember, according to books online you can create a nonclustered columnstore on up to 1024 columns. That means, when creating a non-clustered columnstore index, you don't create an index on a key in the same way you do a standard index, you create a columnstore index on a set of columns.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply