1

I have warehouse table with around 600 000 rows, and 20 columns. Number of rows tend to grow pretty fast. I usually need to execute just this query: 

SELECT * FROM MyTable WHERE EventDate BETWEEN @Date1 AND @Date2

Currently I have just clustered index on primary key and no more indexes. This query for a year period which returns around 200 000 rows executes around 6 minutes, which is really slow for me. Could columnstore index help me and on which column to put it, which columns to include?

bambi
  • 1,159
  • 2
  • 14
  • 31

1 Answers1

0

Are you looking for Clustered columnstore Index or Non-Clustered Columnstore index? If it is Clustered Columnstore index you will be indexing for all the columns. In normal scenario you might not require 'Select *' revisit why you require 'select *' for all cases? Still if it is a datawarehouse workload clustered columnstore will be faster.

Create clustered columnstore index CI_tbl on tablename

Which version of sql server you are using? This feature is available from SQL 2014 where in 2012 you might need to use non clustered columnstore index.

Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
  • Actually I realised that I user Azure standard tier, and that column store indexes are available just in premium tier. – bambi Aug 25 '16 at 13:06