The Maintaining Columnstore Indexes module provides you with the instructions and devices to develop your hands on skills in the following topics.

  • Working with columnstore indexes

Lab time: It will take approximately 40 minutes to complete this lab.

Exam Objectives

Two exam lab objectives are covered in this lab.

  • Demonstrate how to reorganize a columnstore index
  • Demonstrate how to rebuild a columnstore index

Exercise 1 - Working with Columnstore Indexes

SQL Server provides with a concept known as columnstore index that helps you logically organize data as a table with rows and columns. However, internally the system stores the columnstore data in a column-wise data format and performs column-based query processing. You can use the columnstore index to obtain increased query performance for workloads involving large data warehousing fact tables.

However, after one or more data loads (insert, update, or delete operations), the information in an index become scattered in a database. This is known as index fragmentation. When the fragmented indexes increase, the performance of the queries degrades, and may, in turn, reduce an application's performance. You can fix index fragmentation by reorganizing or rebuilding the respective index.

In this demonstration, you will learn how to reorganize and rebuild a columnstore index to reduce index fragmentation. For the purpose of the demonstration, you will perform the following tasks in this exercise:

  1. Create a dimension table named dbo.dimSales_2011.
  2. Load the dbo.dimSales_2011 table with the required data from the FactInternetSales table.
  3. Create a clustered index on a column named OrderDate in the dbo.dimSales2011 table. Convert the clustered index to a clustered columnstore index. This converts the storage for the entire dimSales2011 table from rowstore to columnstore.
  4. Check the fragmentation of the entire indexes in the dimSales_2011 table. Demonstrate the syntax of reorganizing the columnstore index.
  5. Demonstrate the syntax of rebuilding the columnstore index.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.