What is the difference between clustered and non-clustered index?
Understanding the different types of indexes is crucial for optimizing database performance. In SQL, clustered and non-clustered indexes are the two primary types, each serving distinct purposes in how data is stored, organized, and retrieved, significantly impacting query execution times.
Clustered Index
A clustered index determines the physical order of data storage in a table. Because the data rows themselves can only be stored in one order, a table can have only one clustered index. Think of it like a dictionary where the words are physically sorted alphabetically; when you find a word, its definition is right there with it.
- Data rows are stored in the same physical order as their corresponding index keys.
- Each table can have only one clustered index.
- The leaf level of a clustered index contains the actual data pages of the table.
- Typically created on primary key columns, but can be on any column(s).
- Provides fast retrieval for range scans and ordered data.
Non-Clustered Index
A non-clustered index, in contrast, does not alter the physical order of data rows. Instead, it creates a separate structure that contains the index key values and pointers (row locators) to the actual data rows wherever they are stored on disk. Imagine an index at the back of a book; it tells you on which page a topic can be found, but the book's content isn't organized by that index.
- Data rows are stored independently of the index order.
- A table can have multiple non-clustered indexes (up to 999 in SQL Server).
- The leaf level of a non-clustered index contains the index keys and row locators (pointers to the data rows).
- Useful for frequently queried columns that are not part of the clustered index.
- Faster for specific lookups when the query only needs columns included in the index.
Key Differences
| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Physical Order | Determines physical storage order of data | Does not affect physical storage order |
| Number per Table | One | Multiple (up to 999 in SQL Server) |
| Leaf Level | Contains actual data rows | Contains index keys and row locators (pointers) |
| Data Storage | Data is sorted and stored physically in index order | Data is stored separately; index contains pointers to data |
| Purpose | Optimizes range scans and retrieval of entire rows | Optimizes lookups on specific columns, acts as a 'lookup table' |
In summary, while both clustered and non-clustered indexes improve query performance, they do so by organizing data differently. Choosing the right type of index depends on the specific query patterns and data access needs of your application, with each having distinct use cases for optimal database efficiency.