Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

columnstore index - add all columns? Expand / Collapse
Posted Monday, March 31, 2014 4:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 20, 2016 3:52 AM
Points: 927, Visits: 2,039
When creating a column store index, are there any reasons not to include all columns, besides index size of course?
i.e. will the index be more versatile with more columns or should I treat it exactly like its a standard index, putting only necessary columns, in the correct order?
Post #1556410
Posted Monday, March 31, 2014 4:34 AM This worked for the OP Answer marked as solution



Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 15,510, Visits: 13,170
Because of the way the columnstore index works, the order of the columns is not important.
Typically you would try to include all possible columns, at least all the ones which are referenced by SELECT queries.
The problem is that once you have created the index, you cannot simply add columns; you'd have to drop the index and create it again with the additional column.

Data is greatly compressed, so index size is less affected by additional columns than a regular index.

There are restrictions on some data types, so in practice it might not be possible to include all of the columns.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1556413
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse