Indexing Presentation – The Basics

Attached is the slide deck for a short presentation on indexing that was created to educate software developers on the basics of indexing.

Outline:

• What is an Index
• Types of Indexes
• Non Clustered Indexes
• Clustered Indexes
• When To Avoid A Clustered Index
• Covered Indexes
• Index Usage Terminology
• Summary
• Additional Topics

Download Indexing_Overview.pdf

Here is the text outline of the presentation.

Indexing Overview Presentation

Presented by Steve Stedman

SQL Server Database Consultant

Indexing Overview

• What is an Index
• Types of Indexes
• Non Clustered Indexes
• Clustered Indexes
• When To Avoid A Clustered Index
• Covered Indexes
• Index Usage Terminology
• Summary
• Additional Topics

What is an Index

• Similar to the index at the back of a book.
• An index provides a shortcut to get to your data.
• Without an index to find a specific row in a table, it would required a full table scan.
• Types of Indexes

Two types of indexes in SQL Server

• Non-Clustered Indexes
•        Traditional Indexing – contains pointers to the data.
• Clustered Indexes
•        Reorganizes the actual data on disk.

Non Clustered Indexes

• Not required, but clustered indexes are recommended
• Contain only the data specified in the index.
• Do not change the base layout of the tables.
•      Index structure is separate from the base table.
• Use pointers to get to the data.
• Can be created on most data types including char(), varchar(), and uniqueidentifiers.
• Only one non-clustered index can be used per table reference in a query.
• Can improve performance with multiple columns.

Clustered Indexes

• Causes base table structure to change.
• Only one clustered index per table.
• Not required on a table.
• Should never contain char(), varchar(), varbinary(), uniqueidentifiers, or other large or widely distributed identifiers.
• Can significantly increase the size of a table and the database.
• Can increase performance if used correctly.

When To Avoid A Clustered Index

• If you already have a clustered index on a table you can’t create a second one.
• Never use a clustered index on a GUID / UniqueIdentifier

Covered Indexes

• Returns query results without accessing the base table.
• Can lead to major performance increases.
• Applies to Non-Clustered Indexes.

All columns requested in the query are somewhere in the index regardless of :

• Where they are in the query
• Where they are in the index

Index Usage Terminology

• An Index Scan accesses all the rows in the index.
• An Index Seek uses selective rows in the index.
• The Seek is much quicker than the scan.

Summary

• What is an Index
• Types of Indexes
• Non Clustered Indexes
• Clustered Indexes
• When To Avoid A Clustered Index
• Covered Indexes
• Index Usage Terminology
• Summary
• Additional Topics

Additional Topics

• Determining Index Usage
• Indexing for Preformance
• When an Index is not used

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!