Managing Indexes

Practice Labs Module
Time
1 hour 2 minutes
Difficulty
Intermediate

The "Managing Indexes" module provides you with the instructions and devices to develop your hands-on skills in the following topics: Handling indexes, Working with types of indexes.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Overview

Introduction

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

  • Handling indexes
  • Working with types of indexes

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

Exam Objectives

Four exam objectives are covered in this lab.

  • Detecting fragmentation of an Index
  • Reorganize/rebuild an index
  • Defragmenting a columnstore index
  • Drop a columnstore index

Exercise 1 - Handling Indexes

Consider a table Employee with records of 1000 employees. The records consist of the employee ID, name, designation, and their phone numbers. Suppose if you have to find the phone number of a particular employee, you will query the entire table to find the same. The query will scan each and every employee in the table to extract the appropriate records. Querying such a large table increases the data retrieval time and affects the query performance.

SQL offers indexes as a solution that increases the query performance and also reduces the data retrieval time. Indexes are temporary look-up tables that point to an existing table in the database. Querying the index instead of large tables improves the query performance and retrieves the results at a faster rate. Indexes can be created on one or more fields of the table.

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 detect index fragmentation with the help of sys.dmdbindexphysicalstats dynamic management view. You can fix index fragmentation by reorganizing or rebuilding the respective index.

In this exercise, you will detect fragmentation of an index in AdventureworksDW2016CTP3 database tables. You will then reorganize and rebuild the fragmented index.

Exercise 2 - Working with Types of Indexes

There are various types of indexes in SQL Server. Some of them are as follows:

Clustered index: This type of index sorts the records in the physical order of the values in the primary key field of the table. Clustered index improve the query performance of the tables in the database. It is ideally recommended to create clustered index on database tables. Clustered index also help to control table fragmentation. For example, clustered index can sort and order the records in the Employee table based on the Employee ID field. There can only be one clustered index per table. Clustered indexes are automatically created when a primary key constraint is defined for a field in the table. The syntax to create clustered index is as follows: CREATE CLUSTERED INDEX ON <table name (column name)> Columnstore index: This type of 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.

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

Learning Partner
Comprehensive Learning

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