Tables are defined as objects in SQL Server databases. Tables consist of columns and rows of data, and each column has a native or user-defined data type. Tables have two units of data storage: data pages and extents. Data pages are the fundamental units of data storage. Extents are the basic units in which space is allocated to tables and indexes. Data within tables can be organized using partitions.
For all data types except large object data types, table data is stored in data pages that have a fixed size of 8 KB (8,192 bytes). Each data page has a page header, data rows, and free space that can contain row offsets. The page header uses the first 96 bytes of each page, leaving 8,096 bytes for data and row offsets. Row offsets indicate the logical order of rows on a page, which means that offset 0 refers to the first row in the index, offset 1 refers to the second row, and so on. If a table contains text and image data, the text or image may not be stored with the rest of the data for a row. Instead, SQL Server can store a 16-byte pointer to the actual data, which is stored in a collection of 8-KB pages that are not necessarily contiguous. This same technique is used with variable-length columns when the row data exceeds 8 KB.
SQL Server 2005 supports six types of data pages:
Bulk Changed Map. Contains information about extents modified by bulk operations since the last log file backup.
Data. Contains data rows with all data except for nvarchar(max), varchar(max), varbinary(max), and xml data (as well as text, ntext, and image data when text in row is set to ON).
Differential Changed Map. Contains information about extents that have changes since the last database backup.
Global Allocation Map, Shared Global Allocation Map. Contains information about extents that have been allocated by SQL Server.
Index Allocation Map. Contains information about extents used by a table or index.
Index. Contains index entries.
Page Free Space. Contains information about free space available in data pages.
Text/Image. (large object data types) Contains text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data as well as data for variable-length columns when the data row exceeds 8 KB (varchar, nvarchar, varbinary, and sql_variant).
Within data pages, SQL Server stores data in rows. Data rows do not normally span more than one page. The maximum size of a single data row is 8,096 bytes (including any necessary overhead). Effectively, this means that the maximum size of a column is 8,000 bytes, not including large object data types, and that a column can store up to 8,000 ASCII characters or up to 4,000 2-byte Unicode characters. Large object data type values can be up to 2 GB in size, which is too large to be stored in a single data row. With large object data types, data is stored in a collection of 8-KB pages, which may or may not be stored contiguously.
Although collections of pages are ideal for large object data that exceeds 8,096 bytes, this storage mechanism is not ideal when the total data size is 8,096 bytes or less. In this case, you will want to store the data in a single row, and to do this, you must set the text in row table option. The text in row option allows you to place small text, ntext, and image values directly in a data row instead of in separate pages. This can reduce the amount of space used to store small text, ntext, and image data and can also reduce the amount of disk input/output (I/O) needed to retrieve the values.
Note
A table that has fixed-length rows always stores the same amount of rows on each page. A table with variable-length rows, however, stores as many rows as possible on each page, based on the length of the data entered. As you might expect, there is a distinct performance advantage to keeping rows compact and allowing more rows to fit on a page. With more rows per page, you will have an improved cache-hit ratio and reduce I/O.
An extent is a set of eight contiguous data pages, which means extents are allocated in 64-KB blocks and there are 16 extents per megabyte. SQL Server 2005 has two types of extents:
Mixed extents. With mixed extents, different objects can own pages in the extent. This means that up to eight objects can own a page in the extent.
Uniform extents. With uniform extents, a single object owns all the pages in the extent. This means that only the owning object can use all eight pages in the extent.
When you create a new table or index, SQL Server allocates pages from a mixed extent to the new table or index. The table or index continues to use pages in the mixed extent until it grows to the point at which it uses eight data pages. When this happens, SQL Server changes the table or index to uniform extents. As long as the table or index continues to use at least eight data pages, it will use uniform extents.
In SQL Server 2005, tables are contained in one or more partitions, and each partition contains data rows in either a heap or clustered index structure. Partitioning large tables allows you to manage subsets of the table data and can improve response times when working with the table data. To improve read/write performance, you can place partitions into multiple filegroups as well.
By default, tables have only one partition. When a table has multiple partitions, the data is partitioned horizontally so that groups of rows are mapped into individual partitions based on a specific column. For example, you might partition a Customer_Order table by purchase date. In this example, you would split the partition on date ranges, and each partition could hold yearly, quarterly, or monthly data.
For a Customer table, you might partition by customer ID. You would split the partition on name ranges, and each partition would store customer data that starts with a certain character, such as A, B, C, D and so on, or a character sequence such as Aa to Ez, Fa to Jz, Ka to Oz, Pa to Tz, and Ua to Zz.