Creating Indexes

Introduction

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

• Creating index on table
• Working with key columns
• Managing non-key columns

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

Exam Objectives

Four exam objectives are covered in this lab.

• Demonstrate how to create indexes based on the given tables, queries
• Demonstrate how to create a non-clustered index with key columns
• Demonstrate how to create a non-clustered index with included non-key columns
• Demonstrate how to create an index based on the query execution plan

Exercise 1 - Creating Index on Table

Consider a table Employee with records of 1000 employees. The records consist of the employee ID, name, designation and their phone numbers. Consider, you have to find the phone number of a particular employee. You 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. There are two types of indexes that are as follows:

• Clustered index: It sorts the records in the physical order of the values in the primary key field of the table. 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)>

• Non-clustered index: It stores the values of the field from a table. In addition, it stores a pointer to the row in the source table where the value is actually stored. For example, a non-clustered index on the Employee ID will store the values of the field. It will also store a pointer to the row in the Employee table where the value is stored. A table can have multiple non-clustered indexes. The syntax to create non-clustered index is as follows:

CREATE NONCLUSTERED INDEX ON <table name (column name)> GO

In this exercise, you will learn to create a non-clustered index on an existing table.

Exercise 2 - Working with Key Columns

Non-clustered indexes can be used with key columns from the tables to retrieve faster results. Indexing the key columns of the table helps the query to locate the record easily. Indexed key columns are included in the WHERE clause of the SELECT statement.

In this exercise, you will learn to use key columns in a non-clustered index.

Exercise 3 - Managing Non-Key Columns

The query performance can be further enhanced by including non-key columns in the index definition. With the inclusion of the non-key columns, the range of the query can be extended to other fields in the table. Querying the non-clustered index with non-key columns reduces the data retrieval time. This is because the query can locate all the requested fields within the index. As a result, the table does not need to be looked up each time the non-key columns are queried. The non-key columns are added to the index using the INCLUDE clause.

In this exercise, you will learn to include non-key columns in the index definition.