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!