Objects in a Microsoft® SQL Server™ version 7.0 database are stored as a collection of 8 KB pages. This topic describes how the pages for tables and indexes are organized.
The data for each table is stored in a collection of 8 KB data pages. Each data page has a 96-byte header containing system information such as the ID of the table that owns the page and pointers to the next and previous pages for pages linked in a list. A row offset table is at the end of the page. Data rows fill the rest of the page.
SQL Server 7.0 tables use one of two methods to organize their data pages:
The data rows are stored in order based on the clustered index key. The data pages are linked in a doubly-linked list. 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 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.
SQL Server also supports up to 249 nonclustered indexes on each table. 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 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 and indexes are anchored by page pointers in the sysindexes table. Every table has one collection of data pages, plus additional collections of pages to implement each index defined for the table.
Each table and index 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 table and index 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 or index.
Each table has a set of rows in sysindexes:
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.
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.
The values for indid in the rows for each nonclustered index range from 2 to 251. The root column points to the top of the nonclustered index B-tree.
The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages.
In earlier versions of SQL Server, sysindexes.first always pointed 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 7.0, sysindexes.first is largely unused. In earlier versions of SQL Server, sysindexes.root in a row with indid = 0 pointed to the last page in a heap. In SQL Server 7.0, sysindexes.root in a row with indid = 0 is unused.