There are basically two types of indexes:-
- Clustered Indexes.
- Non-Clustered Indexes.
In clustered index the non-leaf level actually points to the actual data.In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data.
The correct answer to your question would be the difference between clustered and non-clustered indexes.
Clustered Index:
- A clustered index determines the physical order of data rows in a table based on the indexed column(s).
- Each table can have only one clustered index because the data rows themselves are sorted based on the clustered index key.
- Because the data rows are physically ordered, there is no need for additional lookups to find the actual data; hence, it can provide faster retrieval of data.
- When you create a primary key constraint in a table, by default, SQL Server creates a clustered index on that column unless a non-clustered index is explicitly specified.
Non-Clustered Index:
- A non-clustered index is a separate structure from the actual data rows and stores a sorted list of references to the data rows.
- Multiple non-clustered indexes can be created on a single table.
- Non-clustered indexes are typically faster for read operations that involve searching for specific values but slower for insert, update, and delete operations because they require maintaining the index structure separate from the data.
- They are beneficial for columns that are frequently searched but not frequently updated.
In summary, the main difference lies in how the data is physically organized. Clustered indexes dictate the physical order of data rows in a table, while non-clustered indexes store a separate sorted list of references to the data rows.