Indexes are generally used to increase the performance of the data warehouse workload. SQL Server by default uses row based storage for its objects. Row based storage is not sufficient to increase the performance of the workload for the data warehouse tables which contain millions of rows because all columns for each row stored together. If query wants to fetch only a few columns, then because of storage mechanism all columns will be read into the buffer pool. Microsoft Introduced column based storage first time in SQL Server 2012 in terms of non-clustered ColumnStore indexes. In this lab we will learn various aspects about non-clustered ColumnStore indexes. In the first exercise, we will learn about the difference between row based and column based storage mechanism. We will also learn about best practices and limitations of non-clustered ColumnStore indexes. In the second exercise, we will learn about batch mode processing and data compression to improve the performance. In SQL Server 2014, Non-clustered ColumnStore indexes are non-updatable. In the third exercise, we will learn about workarounds to implement updatable non-clustered ColumnStore index. In fourth and final exercise, we will learn performance differerences between nonclustered rowstore index, nonclustered columnstore indes and indexed view.
Exercise 1: Introduction of Non-clustered ColumnStore Indexes
Exercise 2: Benefits of using Non-clustered ColumnStore Indexes
Exercise 3: Updating NonClustered ColumnStore Index
Exercise 4: NonClustered ColumnStore Index versus Indexed View