Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

columnstore index - add all columns? Expand / Collapse
Author
Message
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: Wednesday, November 19, 2014 8:30 AM
Points: 907, Visits: 1,898
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
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 LessThanDot.

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

Add to briefcase

Permissions Expand / Collapse