1

If the SQL Server clustered index is the physical order of the table and all columns are included, is it the table itself? How is the clustered index stored physically?

I have seen the Q & A What is a Clustered Index? but my question is about the physical organization of these, not their function.

0
6

Don't get caught up the the physical/logical aspect. It is just terminology and what you mean by "physical" is just a matter of abstraction level in the end. IMO, the term "physical" is meaningless, confusing and should be avoided in this context.

Both types of indexes are B-Tree indexes. Make sure you understand what this is and that you can visualize a B-Tree index.

For the clustered, the leaf contains not only the key, but also all the other columns, making it the "table itself".

For a non-clustered index, the leaf has only the key columns and included columns - along with a pointer to the actual data.

The leaf is linked where the first page (8 KB) points to the next, etc (a doubly linked list). As modifications are done, you might get page splits where a page is inserted into this linked list, so looking at the page numbers in the file, you jump back and forth when following the linked list from beginning to the end (using the pointers). This applies to both clustered and non-clustered indexes.

5

The clustered index is the logical ordering of the data of the table itself into a B-Tree structure. Without a clustered index, the table itself is then stored in a Heap data structure, logically. This article, Clustered Index vs Heap in SQL Server, goes into the structural differences between the two.

One other thing to note is the clustered index also specifies a deliberate ordering to the data, when logically stored, whereas a table without a clustered index, and therefore logically stored as a Heap, will typically have no order and therefore is randomly sorted logically.

The benefits of utilizing a clustered index on a table, when architected properly, is that it can be used to improve performance of queries that it covers when the SQL Engine can seek on it for the predicates of those queries, as opposed to a table without a clustered index that would end up requiring a full scan.

To your question regarding the differences between a clustered index and nonclustered index: the clustered index sorts the table itself, logically, the nonclustered index sorts a copy of the data (whatever fields that nonclustered index covers and includes) from the table, logically.

Regardless of whichever logical data structure that is used, physically the data is stored in Pages and Extents on disk. Pages are typically sets of 8 KB of data, whereas Extents are a collection of 8 physically contiguous Pages.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.