Level: Intermediate

Kevin Goff

Microsoft SQL Server MVP

Practice Manager

SetFocus, LLC

Learn everything you need to know about new columnstore index in SQL Server 2012 and the enhancements to the columnstore index in SQL Server 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. I'll also 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 readonly index in 2012. Finally, I'll show some of the enhancements to the columnstore index in SQL Server 2014, including the ability to define a columnstore index as read-write.

You will learn:

• Where environments can best leverage the columnstore index
• Which queries can take optimum advantage of the columnstore index, and which ones can't
• What a columnstore index really is "under the hood", how SQL Server works with it, and how SQL Server can generate such huge performance gains
• The enhancements in SQL Server 2014