6-1 Indexes (Clustered, Non-Clustered) Explained

Key Concepts

• Indexes
• Clustered Indexes
• Non-Clustered Indexes

Indexes

Indexes are data structures that improve the speed of data retrieval operations on database tables. They work similarly to the index in the back of a book, allowing the database engine to quickly locate the data without having to scan the entire table.

Clustered Indexes

A clustered index determines the physical order of data in a table. Each table can have only one clustered index because the data rows themselves can be stored in only one order. The leaf nodes of a clustered index contain the actual data rows.

Example: In a table of employees sorted by EmployeeID, the EmployeeID column can be the clustered index. This means the rows are physically stored in the order of EmployeeID.

Analogies: Think of a clustered index as organizing a bookshelf by the author's last name. All books by the same author are grouped together, and the entire shelf is sorted by this criterion.

Non-Clustered Indexes

A non-clustered index is a separate structure from the data rows. It contains the index key values and pointers to the data rows. A table can have multiple non-clustered indexes. The leaf nodes of a non-clustered index contain the index key values and row locators that point to the data rows.

Example: In the same employee table, you might create a non-clustered index on the Department column. This index would allow quick lookup of employees by department without affecting the physical order of the rows.

Analogies: Think of a non-clustered index as an index card system in a library. Each card contains a keyword (like a department name) and a reference (like a row locator) to the actual book (data row) on the shelf.

Conclusion

Understanding the differences between clustered and non-clustered indexes is crucial for optimizing database performance. Clustered indexes determine the physical order of data, while non-clustered indexes provide a faster lookup mechanism without altering the data's physical structure. By judiciously using both types of indexes, you can significantly enhance the efficiency of your database queries.