Are heaps considered an index structure or are they strictly a table structure without index?

Question

Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505

Heaps: Are they considered an index structure or are they strictly a table structure without index?

Answer 1

From MSDN - Table and index organization:

"SQL Server tables use one of two methods to organize their data pages within a partition:

• Clustered tables are tables that have a clustered index.

The data rows are stored in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. The pages in each level of the index, including the data pages in the leaf level, are linked in a doubly-linked list. However, navigation from one level to another is performed by using key values.

• Heaps are tables that have no clustered index.

The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list."

Heaps - SQL Server Optimization:

"A heap table, by definition, is a table that doesn't have any clustered indexes. Different pages of the heap-based table occupy different non-contiguous areas on a disk, and they are not linked together in any way."

As opposed to - Clustered Index Structures: "In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list."

Other references:

• Fragmentation (part 4): what are heaps?

• SQL Server heap v.s. clustered index

• Heaps

• Clustered Tables vs Heap Tables

Answer 2

An index implies there is data order to the rows. Given that a heap isn't created with any such order and doesn't maintain an order either, it's just a method of storing table data.

There are a few examples (say, dm_db_index_physical_stats) where to specify a heap, you have to enter an index id of 0. While this seems to contradict what I just said, I think this is just a magic value to simplify the API and its usage; nothing more.

Answer 3

Table structure without a clustered index.