Extensible Storage Engine - Tables

Tables

A table is a homogeneous collection of records, where each record has the same set of columns. Each table is identified by a table name, whose scope is local to the database in which the table is contained. The amount of disk space allocated to a table within a database is determined by a parameter given when the table is created with the CreateTable operation. Tables grow automatically in response to data creation.

Tables have one or more indexes. There must be at least one clustered index for record data. When no clustered index is defined by the application, an artificial index is used which orders and clusters records by the chronological order of record insertion. Indexes are defined to persist interesting orders of data, and allow both sequential access to records in index order, and direct access to records by index column values. Clustered indexes in ESE must also be primary, meaning that the index key must be unique.

Clustered and non-clustered indexes are represented using B+ trees. If an insert or update operation causes a page to overflow, the page is split: a new page is allocated and is logically chained in between the two previously adjacent pages. Since this new page is not physically adjacent to its logical neighbors, access to it is not as efficient. ESE has an on-line compaction feature that re-compacts data. If a table is expected to be frequently updated, space may be reserved for future insertions by specifying an appropriate page density when creating a table or index. This allows split operations to be avoided or postponed.