INFO: Explanation of Index ID's Found in SYSINDEXES (75191)

MORE INFORMATION

The pages allocated to an object are subdivided with an "index ID" tag that is stored on every page. All of the pages with a particular indid are chained together. The sysindexes table contains a separate row for each indid so the beginning page and ending page in the chain can be found, and the root page and distribution page can be found for indexes.

Pure data pages have indid=0. Clustered index pages and the data pages have indid=1 (remember, the leaf of a clustered index is the data). However, there can never be indid=0 and indid=1 at the same time (except, perhaps during the creation of a clustered index on a table that already contains data).

Text and image pages need to be chained together independently of the data page chains and index page chains. Indid=255 is used to tag these types of chains. If a single table has several text or image columns, there is still only one indid=255 entry in sysindexes. The "first" column in sysindexes can no longer be considered head-of-chain for indid=255.

The indid is stored on each page in a single byte. All text or image pages for a particular table have indid=255. Another byte is normally used for the number of the index tier this index page belongs to (leaf=0).

On text/image pages, the byte is used for a "sub-indid" number. All text or image pages have indid=255, but the first text or image column has "sub-indid"=255; the second text or image column in that table has "sub-indid"=254, and so on.