CODEWITHSUNDEEP
sql
user544079
user544079

Reputation: 16639

Clustered Indexes physical storage

Can a Clustered index store the actual physical data in the order of the index?

Upvotes: 1

Views: 538

Answers (5)

gbn
gbn

Reputation: 432471

Extending other answers...

  • the data is in order of index key. Non-key columns have no order
  • the order is logical: insert a new row in the middle and you may get a page split and some pointers
  • physical order will exist after create or rebuild (subject to partitioning etc)
  • the output order will only be guaranteed by ORDER BY

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74315

Most SQL database implementations structure a database table without indices in the form of a heap — an unordered collection of related pages, each containing some number of rows.

Non-clustered indices are B-trees, the leaf nodes of which contain pointers to the data pages containing that key value. Getting a non-key value via a non-clustered index requires an additional lookup to retrieve the data page in question.

Clustered indices, like non-clustered indices, are B-trees, with one important difference: the heap goes away. The leaf nodes of the clustered index are the data pages for the table: thus giving physical order to the table. The rows on each page, likewise are maintained in key order. Getting a non-key value via a clustered index doesn't require the overhead of of the additional lookup required to get the data page in question.

Upvotes: 1

JNK
JNK

Reputation: 65187

That's what they do.

A clustered index is all the data in the table, stored in the physical order of the clustering key and with a supporting b-tree structure for quick navigation.

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

From MSDN

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

So yes, a key point of a Clustered Index is to store the physical data in the order of the index.

Upvotes: 0

HardCode
HardCode

Reputation: 6766

A clustered index is an exact copy of the table, sorted by the field(s) of the index.

Upvotes: 0

Related Questions