Is the actual table data read during clustered index scan in SQL server or just the index pointers?

Question

I understand that clustered index create a B-tree and the actual data is stored in the leaf's connected as a doubly linked list.

But when there is an index scan (selecting data from the table without any "where" clause), does the SQL server read only the index pointer(non-leaf nodes) or does it actually read the data.

My execution plan shows that the clustered index scan got 1 GB of data, which is almost same as my table size. As per my understanding, the SQL index scan should be getting all the actual Table data. Am I missing something here.

Answer 1

Clustered index itself is the table,so table is read..Index pointers are just used to navigate through BTree

Answer 2

Clustered index itself is the actual table...

If you create a table directly with no clustered index, it is called a heap - which is just some unorganized (not ordered) set of pages. Each page would be pointing to the previous page and the next page (doubly linked list).

Now imagine you create a clustered index for that table:

All the pages are now stored in an order of the key specified for the cluster -> these are leaf level pages and contain actual data in every row. These still use the doubly linked list.

In addition, the clustered index structure will involve extra pages in upper levels (could be more than one level) so that they form a balanced tree -> these are branch pages and root pages. Data derived from just the clustered key are used as pointers to the pages in lower levels.

This formation is so that SQL engine can locate a page necessary for finding the data easily (Called SEEK operation) e.g. when you execute a query which uses a predicate matching the cluster key, it would be able to efficiently locate the exact data.

If the key doesn't match or if SQL knows that table is small enough (or even when it knows it is returning almost the whole table data), it doesn't have to use the upper level pages. It might decide to directly go to the leaf level pages to scan all rows and find out matching records. Remember double linked lists to point to the previous and next page.

Bonus: there could be an index scan even when you specify a WHERE clause because it couldn't use the seek or SQL thought it's more efficient to scan than the seek.

Let me know if this helps.