Creating Columnstore Indexes

Introduction

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

• Working with clustered columnstore indexes
• Managing nonclustered indexes on clustered columnstore indexes
• Working with nonclustered columnstore indexes

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

Exam Objectives

Three exam objectives are covered in this lab.

• Demonstrate how to create a clustered columnstore index for a large dimension table
• Demonstrate how to define a nonclustered index on a clustered columnstore index
• Demonstrate how to create a nonclustered columnstore index for real time performance analysis

Exercise 1 - Working with Clustered Columnstore Indexes

A columnstore index is one of the features in SQL Server 2016 that enables you to manage, store, and extract data from the database. This feature uses a columnar data format called the columnstore. The columnstore is a logical table with rows and columns, physically stored in a column-wise format. The rowstore is a logical table with rows and columns, physically stored in a row-wise format.

You can perform data compression at a very high level using the columnstore indexes. You can create clustered and nonclustered columnstore indexes in the database.

A clustered columnstore index refers to the physical storage of the table in the database. The syntax to create a clustered columnstore index is as follows:

CREATE CLUSTERED COLUMNSTORE INDEX index_name ON [database_name. [schema_name ] . | schema_name . ] table_name [ WITH ( < with_option> [ ,...n ] ) ] [ ON <on_option> ] [ ; ]

In this exercise, you will work with clustered columnstore indexes.

Exercise 2 - Managing Nonclustered Indexes on Clustered Columnstore Indexes

You can create nonclustered indexes in a database to search the data efficiently and quickly. Nonclustered indexes improve the performance of frequently used queries. You can create multiple nonclustered indexes on tables or views in the database.

In this exercise, you will manage nonclustered indexes on clustered columnstore indexes.

Exercise 3 - Working with Nonclustered Columnstore Indexes

The feature of real-time operational analytics is introduced in SQL Server 2016. This feature allows you to run Online Analytical Processing (OLAP) and Online Transaction and Processing (OLTP) on the same database tables at the same time. You need not have two separate systems to handle the OLAP and OLTP workloads if you use real-time operational analytics.

You must create nonclustered columnstore indexes (NCCI) to use real-time operational analytics. Nonclustered columnstore indexes maintain separate copies of database data. Therefore, OLAP and OLTP can run in parallel on the data.

In this exercise, you will work with nonclustered columnstore indexes.