Table and Index Architecture

Objects in a Microsoft® SQL Server™ 2000 database are stored as a collection of 8-KB pages. This topic describes the way the pages for tables and indexes are organized.

SQL Server 2000 supports indexes on views. The first index allowed on a view is a clustered index. At the time a CREATE INDEX statement is executed on a view, the result set for the view is materialized and stored in the database with the same structure as a table that has a clustered index. The result set that is stored is the same as that which is produced by this statement.

SELECT * FROM ViewName

The data rows for each table or indexed view are stored in a collection of 8-KB data pages. Each data page has a 96-byte header containing system information such as the identifier (ID) of the table that owns the page. The page header also includes pointers to the next and previous pages that are used if the pages are linked in a list. A row offset table is at the end of the page. Data rows fill the rest of the page.

Organization of Data Pages

SQL Server 2000 tables use one of two methods to organize their data pages:

• Clustered tables are tables that have a clustered index.

  The data rows are stored in order based on the clustered index key. The 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, but navigation from one level to another is done 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.

Indexed views have the same storage structure as clustered tables.

SQL Server also supports up to 249 nonclustered indexes on each table or indexed view. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables and indexed views keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.

The pages holding text, ntext, and image data are managed as a single unit for each table. All of the text, ntext, and image data for a table is stored in one collection of pages.

All of the page collections for tables, indexes and indexed views are anchored by page pointers in the sysindexes table. Every table and indexed view has one collection of data pages, plus additional collections of pages to implement each index defined for the table or view.

Each table, index and indexed view has a row in sysindexes uniquely identified by the combination of the object identifier (id) column and the index identifier (indid) column. The allocation of pages to tables, indexes, and indexed views is managed by a chain of IAM pages. The column sysindexes.FirstIAM points to first IAM page in the chain of IAM pages managing the space allocated to the table, index or indexed view.

Each table has a set of rows in sysindexes:

• A heap has a row in sysindexes with indid = 0.

  The FirstIAM column points to the IAM chain for the collection of data pages for the table. The server uses the IAM pages to find the pages in the data page collection because they are not linked together.

• A clustered index on a table or view has a row in sysindexes with indid = 1.

  The root column points to the top of the clustered index B-tree. The server uses the index B-tree to find the data pages.

• Each nonclustered index created for a table or view has a row in sysindexes.

  The values for indid in the rows for each nonclustered index range from 2 through 250. The root column points to the top of the nonclustered index B-tree.

• Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255.

  The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages.

In SQL Server version 6.5 and earlier, sysindexes.first always points to the start of a heap, the start of the leaf level of an index, or the start of a chain of text and image pages. In SQL Server version 7.0 and later, sysindexes.first is largely unused. In SQL Server version 6.5 and earlier, sysindexes.root in a row with indid = 0 points to the last page in a heap. In SQL Server version 7.0 and later, sysindexes.root in a row with indid = 0 is unused.