SQL Server 2012 introduced columnstore indexes, which can greatly decrease query processing time in OLAP databases. Basically, data is stored by columns rather than by rows, which can allow for faster querying for aggregates and totals. Storing by column also allows for greater compression of the data. This is an Enterprise edition feature. More background is at TechNet (although this for SQL Server 2012).
One drawback was that the indexes were not updatable, so data couldn’t be added without first dropping the index. For SQL Server 2014, the indexes will now be updatable. Also, clustered columnstore indexes are available.
As a test, I created a table to store requests for tickets for upcoming football games.
create table TicketWaitList ( RecordId int not null identity(1,1), HomeState char(2) not null, HomeCity varchar(20) not null, FavoriteTeam varchar(20) not null, GameNumber tinyint not null, TicketQuantity tinyint )
It tracks the city and state of the customer making the request, along with their favorite team and how many tickets they wanted to purchase.
I ran two queries, one to aggregate all the data, and a second to return the number of requests per state.
select HomeState, HomeCity, FavoriteTeam, GameNumber, sum(TicketQuantity) as TotalTickets from TicketWaitList group by HomeState, HomeCity, FavoriteTeam, GameNumber;
select HomeState, sum(TicketQuantity) as TotalTickets from TicketWaitList group by HomeState;
Each query was run three times, one with a standard rowstore index, and with a columnstore index. The table contains 2.7 million records.
create nonclustered index IX_TicketWaitList on TicketWaitList (HomeState, HomeCity, FavoriteTeam, GameNumber); create clustered columnstore index IX_TicketWaitList_ColumnStore on TicketWaitList;
Adding the rowstore requires all other indexes be dropped. I threw out the first run for each query (the columnstore query took a while to run the first time) to allow the execution plan to be cached.
Results – times in milliseconds
Query #1: 2170, 2170, 2196
Query #2: 776, 763, 783
Query #1: 110, 43, 43
Query #2: 36, 30, 40
As you can see, the columnstore indexes allow for 15-20 times greater speed on these queries.
I’ve posted the entire SQL script at: