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.
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.
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.dm_db_index_physical_stats 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
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.
IT & Cybersecurity certification hands on practice labs and practice exams for certifications and skill development.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.