Hello Friends,

Here is another popular lab from Hands-On-Lab library: Non-Clustered ColumnStore Indexes in SQL Server 2014.

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

SQLMaestros Hands-On-Labs enables the practical way of learning. Each lab document consists of multiple exercises and each exercise consists of multiple tasks. Apart from step-by-step instructions, the HOL document contains images, explanations, observations, notes, tips & tricks to ensure a great learning experience. With each lab, you can practice a specific concept step-by-step. With our HOL documents, you will get hands-on with hundreds of topics in SQL Server & Microsoft Data Platform. Learn More.

Learn More

 

 

Happy Learning – The Practical Way.