May 14, 2014 at 1:57 pm
I apologize if this has been talked about previously, but my question should be a relatively simple one to answer for those who know a little more about them than I do. With the introduction of Clustered Column Store Indexes in SQL 2014, what situation, if any, would it be better to use the Nonclustered Column Store Index?
May 14, 2014 at 3:29 pm
The big one for me would be that you can just add a nonclustered columnstore index to the table, but still have the table stored as a regular SQL Server table. The clustered columnstore turns it into a different storage mechanism. So, if you need to have a mixed access table, the nonclustered columnstore is the way to go. I haven't seen this in the wild though. Another consideration is that you can have mixed indexes on the nonclustered columnstore, although Brent Ozar published a trick that lets you use a view to create nonclustered indexes on a clustered columnstore index.
The big drawback of course is that as soon as you make a nonclustered columnstore index, the underlying table becomes read only.
"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
May 15, 2014 at 12:26 am
Grant Fritchey (5/14/2014)
... So, if you need to have a mixed access table, ....
What do you mean with this?
The table becomes read-only, so you can access it only through SELECT statements.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 15, 2014 at 4:33 am
Koen Verbeeck (5/15/2014)
Grant Fritchey (5/14/2014)
... So, if you need to have a mixed access table, ....What do you mean with this?
The table becomes read-only, so you can access it only through SELECT statements.
Mixed access meaning you have the columnstore index and you have standard clustered indexes and nonclustered indexes. Mixed access AND read only.
"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
May 15, 2014 at 4:43 am
Grant Fritchey (5/15/2014)
Koen Verbeeck (5/15/2014)
Grant Fritchey (5/14/2014)
... So, if you need to have a mixed access table, ....What do you mean with this?
The table becomes read-only, so you can access it only through SELECT statements.
Mixed access meaning you have the columnstore index and you have standard clustered indexes and nonclustered indexes. Mixed access AND read only.
Ah OK. Thanks for the clarification!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 15, 2014 at 8:11 am
Thanks for the response. I am currently working with nonclustered column store indexes in my data warehouse testing, and wanted to make the switch to SQL 2014 in order to have updateable clustered column store indexes, and was just curious if there was any huge benefits to staying nonclustered that I just wasn't aware of, but it doesn't sound like it. Thanks for the help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply