Columnstore Index in SQL Server 2012/2014


It might seem far-fetched that a company would upgrade to SQL Server 2012 “just” for a database index. But after you see the performance enhancements gained from the new columnstore index in SQL Server 2012, you’ll see why many are calling this one of the most important features in the history of SQL Server. In this presentation, I’ll talk about the physical aspects of columnstore index in SQL Server 2012, how to create it, and what environments (mainly data warehousing environments) and queries can make use of it. I’ll show performance benchmarks between columnstore indexes in SQL Server 2012, and compare them to index strategies prior to SQL Server 2012. I’ll also show how the new Batch execution mode can improve performance as well. Finally, I’ll show what queries can take advantage of the columnstore index (and which ones can’t) and how to use partition switching as a workaround for the fact that the columnstore index is a read only index. Time permitting, I’ll talk about the new features in the columnstore Index in SQL Server 2014

Kevin Goff

– Microsoft Data Platform (SQL Server) MVP
– Microsoft MVP since 2005
– Database/Business Intelligence architect/consultant
– Frequent Speaker in the Mid-Atlantic
– Columnist for CODE Magazine (Baker’s Dozen Productivity Series)