Accessing Data Without Indexes: Table Scan

As explained before,table data is stored in pages, and pages are grouped in extents. Special pages called IAM (Index Allocation Map) keep information about which extents are used by a particular table.

Information related to table definition is stored in different system tables:

• The name of the table, as well as the owner, creation date, and other general information, is stored in the sysobjects system table. The sysobjects table has a field called ID, which provides a unique object identification number for every object stored in a database. The ID field is used to relate this object to extra information in other systems'tables. It is possible to retrieve the table ID calling the OBJECT_ID system function.

• Column definitions are stored in the syscolumns system table.

• UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are treated as objects, which is why there is an entry in the sysobjects system table to identify them.

• Other constraints, such as CHECK and DEFAULT constraints, are identified in sysobjects, too, but their definition is stored in the syscomments table.

• The sysindexes table stores information about indexes'definitions. Every index is identified in the sysindexes table by its name, its table ID, and a special value unique to every index on the same table, called indid, with values from 0 to 255.

The sysindexes system table stores important information about how to start reading actual data or index keys. Every table has at least one entry in the sysindexes table.

If a table doesn't have a clustered index, it is stored as a heap, because the rows are not in any specific order. In this case, the table itself is defined in the sysindexes table as a row with indid = 0. The allocation map of a table or index is stored in special pages, called the Index Allocation Map (IAM), and sysindexes stores the address of the first IAM page of a specific table or index in the FirstIAM field.

IAM pages are special SQL Server pages that contain a sequence of bits, which is why they are called bitmaps. Every bit indicates whether the correspondent extent in the file uses the page.

If SQL Server needs to retrieve data from a table that doesn't have any clustered index defined, it uses the IAM pages to select which pages belong to the table and reads the pages in sequence to retrieve the information.

Because the IAM pages reflect the physical order of the data pages, the reading process can be quite efficient. However, using indexes can usually provide better performance than table scans.