Level: Intermediate
In this session Joey will discuss Columnstore Indexes in SQL Server 2014.
Data Warehouse workloads can be very large and taxing on your database server. With the modern demands of real-time analytics against growing volumes of data this challenge continues. When columnstore indexes and batch mode query processing were introduced in SQL Server 2012, they were a performance game changer. In the 2012 implementation of columnstore, direct updating and inserting of data was not supported and other restrictions, such as data types limited the use of the feature. SQL 2014 removes those limitations; you can use a columnstore index as a clustered index, and more importantly directly perform DML against the index. Also, more query operators support batch mode, which means more of your queries can see performance benefits. You will understand the changes for 2014 and how having updateable columnstore indexes may cause changes to your process design. You will see the power of columnstore indexes in analytics, their limits, and how to include them into your system design.
You will learn:
- How SQL Server implements columnstore technology for incredible data warehouse performance
- Best practices around when and where to use columnstore indexes
- An overview of how columnstore indexes operate internally to SQL Server