What does this sentence mean: Clustered indexes are stored physically on the table?

clustered-index, indexing, sql-server

Solution

This means that the data in the table are stored in a `B-Tree` according to the order of the `CLUSTERED PRIMARY KEY` (or the clustering columns).

This name is in my opinion a little bit confusing. The same concept in `Oracle` is called `index-organized table` which I find much more descriptive.

Non-clustered indexes contain the value of the indexed columns along with the pointer to the record they are originated from.

The "clustered index" is the table itself; the "non-clustered" index is an ordered copy of some of the table's columns.

If you "create" a clustered index, the table is rearranged. That's why you cannot have more than one "clustered index" on a table: the table cannot be arranged in more than one order.

If you create a secondary index, the shadow copy of the table is created, holding the values of the indexed columns and the pointers to the records they are from. Whenever the table changes, the copy is changed too (the engine takes care of that automatically).

Non-clustered table

id   col1   value
--   --     --
1    1      Data 1
6    1      Data 6
3    1      Data 3
7    2      Data 7
9    2      Data 9
5    2      Data 5

The table is not ordered.

Clustered table

id   col1   value
--   --     --
1    1      Data 1
3    1      Data 3
5    2      Data 5
6    1      Data 6
7    2      Data 7
9    2      Data 9

The table is ordered on `id`.

Clustered table with a secondary index

Table                      Index
id   col1   value          col1   id
--   --     --             --     --
1    1      Data 1         1      1
3    1      Data 3         1      3
5    2      Data 5         1      6
6    1      Data 6         2      5
7    2      Data 7         2      7
9    2      Data 9         2      9

The table is orderer on `id`, the index is ordered on `(col1, id)`

Problem

Original source