Best Practices in Index Creation

Introduction

The Best Practices in Index Creation module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Managing clustered indexes on varchar columns
• Managing clustered and nonclustered indexes

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

Exam Objectives

Four exam objectives are covered in this lab.

• Demonstrate how creating a clustered index on a varchar column which has existing data in the IN_ROW_DATA allocation unit affects subsequent insert or update actions on the column
• Demonstrate how creating clustered indexes on a table followed by several nonclustered indexes is the best practice rather than creating several nonclustered indexes on a table followed by a clustered indexes
• Create a clustered index on a table followed by several nonclustered indexes
• Create several nonclustered indexes followed by a clustered index and rebuild the nonclustered indexes

Exercise 1 - Managing Clustered Indexes on Varchar Columns

Clustered indexes improve the query performance of the tables in the database. It is ideally recommended to create clustered indexes on database tables. Clustered indexes also help to control table fragmentation. One of the limitations of clustered indexes is that the index column cannot belong to the varchar data type where the data is stored in the ROW_OVERFLOW_DATA allocation unit.

In this exercise, you will learn to manage clustered indexes on varchar columns in the tables.

Exercise 2 - Managing Clustered and Nonclustered Indexes

There are two ways to work with clustered and nonclustered indexes in database tables. The first way is to create clustered indexes and then create multiple nonclustered indexes on tables. The second way is to create multiple nonclustered indexes, create clustered indexes on tables, and then rebuild the nonclustered indexes. Out of these two ways, the second way is a recommended practice that can be adopted in a real-time environment.

In this exercise, you will manage clustered and nonclustered indexes.