|
See Also: Main_Page - Database Administration - Database Design - Indexing
When you execute a CREATE TABLE statement, SQL Server populates numerous system tables with meta-data. You can query the following system views (sometimes also called "catalog views") to retrieve meta-data about tables:
| System View | Description | | Sys.tables | Returns one row for each table. The output includes all columns found in sys.objects. In addition the output also includes data specific to user tables, for example large_value_types_out_of_row column shows how SQL Server is supposed to manage storage for large value data types. | | Sys.indexes | Returns one row for each table, view or index. The output of this view includes clustered, non-clustered and XML indexes. Any table that does not have a clustered index will have a row in sys.indexes with index_id = 0. | | Sys.partitions | Returns one row for every table and index partition. Each table has at least one partition. Any table that does not have a clustered index will have a row in sys.partitions with index_id = 0. Each clustered index will have a row in this view with index_id = 1. Each non-clustered index will have a row with index_id between 1 and 250. Each table with a large column (TEXT, NTEXT, IMAGE data types) will have a row with index id > 250. | | Sys.allocation_units | Returns one row for every allocation unit in the database. Each allocation unit can be either for in row data, row overflow data or LOB data. Every table will have an allocation unit for in-row data. A table may or might not have row-overflow or LOB data allocation units. | | Sys.columns | Retrieves columns for user and system tables, views, and table-valued functions. |
SQL Server 2005 allows you to partition tables and indexes. By default, each table has a single partition, but you can create up-to 1000 partitions for each table and up-to 1000 partitions per each index. When a table has multiple partitions the data is partitioned horizontally, meaning that each partition will have the same columns with identical meta-data. However, each partition will store only a subset of table records.
Each data page contains a page header which stores page meta-data, such as database file identifier, current page number, previous and next page numbers, number of free bytes per page and so forth. After the page header each data page contains actual data rows for in-row data. Tables with lean data types can store more records per page than those tables with variable length columns.
All data rows have identical structure, but the number of columns per record will vary depending on table schema. Here is the basic structure of each data record:
- Each row has a 4-byte record header. Two of these bytes identify the record type - whether it is a primary record, forwarded record, overflow data record, BLOB record, or ghost record. (Ghost records are records that have been affected by the DELETE statement, but they haven't been removed from the leaf level of an index because the transaction hasn't been committed as of yet). The other two bytes store the length of the fixed-width size of the row excluding the NULL bitmap.
- Following the record header, the row contains every fixed-width column; regardless of how many INT, CHAR, NUMERIC or other fixed-width columns you have in a table, they will always be stored together.
- Following the fixed-width columns, the record contains the NULL bitmap. The NULL bitmap has two bytes representing the total number of columns in the record and a variable number of bytes (depending on the number of columns) for storing null-ability of each column whether the column allows NULL values or not.
- Variable-length column offset array comes next. This section contains two bytes representing the total number of variable-length columns. In addition, for each variable-length column there are two more bytes; these identify the ending byte position of the column within the record.
- Each record also has a versioning tag, containing a timestamp and a pointer to the version store in tempdb database.
More SQLServerPedia Articles on Indexes
How SQL Server Indexes Work
Types of Indexes in SQL Server
- Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
- Indexed Views - how to build indexes on top of views for more speed.
- XML Indexes Overview - how to index XML documents and fragments.
- XML Index Rules - some additional considerations and requirements for XML indexes.
Best Practices on How to Design Database Indexes
Maintaining Indexes for Top Performance
Indexes need regular maintenance in order to perform well.
|