This is intended as a brief overview of indexing on SQL Server. Understanding and using indexes can lead to some major performance improvements.
Non-Clustered Indexes
Non-Clustered indexes are often times considered traditional indexing – contains pointers to the data. This is similar to the index at the back of a book. If you open a book to the index, then look up a term you are given the page numbers that the term is on. A non-clustered index is similar to this process, the non-clustered index contains a list of data, then finally it has a link to the actual location for that data similar to the page number in the back of a book.
The Non-Clustered index contain only the data specified in the index, and the primary key information to get to the rest of the data. Non-Clustered indexes do not change the base layout of the tables, they are a copy of the data, and use pointers to get to the data.
Non-Clustered indexes can be created on most data types including char(), varchar(), and uniqueidentifiers. Non-Clustered indexes can improve performance by adding multiple columns.
Clustered Indexes
A clustered index is an index that reorganizes the actual data on disk, causing the entire base table structure to change. Since the clustered index rearranges the base table, only one clustered index is allowed per table.
The term clustering refers to the act of adding a clustered index. Clustering can significantly increase the size of a table and the database if it is not used correctly.
When used correctly clustered indexes can dramatically increase performance.
Covered Indexes
A covered index, is not another type of index, it usually applies to a non-clustered index and the index is considered covering when the index contains everything that a query is looking for without accessing the base table.
Covered indexes can lead to major performance increases since the base table doesn’t need to be accessed.
Index Usage Terminology
Scan: An Index Scan accesses all the rows in the index.
Seek: An Index Seek uses selective rows in the index.
The Seek is much quicker than the scan.
The index scan is usually much quicker than a full table scan.
Additional Index Resources
- Database Health Reports – A free tool to analyze SQL Server health and performance.
- Indexing reports with Database Health Reports.
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!