Index

Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book.

Clustered Index: is a special type of index that reorders the way records in the table are physically stored. Therefore a table can only have 1 clustered index. The Leaf Nodes of a clustered index contain the data pages.

Non-clustered Index: is a special type of index which the logical order of the index does not match the physical order of the rows on disk. The leaf node does not contain the data pages instead it has indexed rows or pointers to the data.

Composite Index: If the Primary Key consists of multiple columns, the database creates an index across all of the columns.

Filtered Index: is used to index a portion of rows to improve query performance, reduce index maintenance and index storage costs compared to full table indexes. A where clause is used to create a Filtered Index.

Included Column in Non-clustered Index: A Non-clustered index can be extended by including nonkey columns in addition to the index key columns. The Nonkey columns are stored at the leaf level of the B-Tree.

Index Selectivity: The selectivity ratio of an index is calculated by dividing the total unique values in the field by the total number of rows in the table. If the selectivity is high (close to 1), then that field is a good candidate for indexing. Look for ratios higher than .8 to use an index.

The SQL Profiler is a very useful tool in helping to analyze indexes and queries. Indexes are used by SQL Server to enforce uniqueness of data in the database tables and to speed up searches. Creating a useful index is one of the most important tasks in designing a database.


B-Tree Stucture