Data pages are used to store data of a table. There are two types of data pages, each of which is used to store data in a different format:
- In-row data pages
- Row-overflow data pages
In-Row Data
Data pages are used to store data of a table. There are two types of data pages, each of which is used to store data in a different format:
- In-row data pages
- Row-overflow data pages
In-Row Data Pages
There is nothing special to say about in-row data pages: they are pages in which it is convenient to store data and index information. All data that doesn’t belong to large objects is always stored in-row. Also,
VARBINARY(max), and XML values can be stored in-row, if the large value types out of row option of the
sp_tableoption system procedure is set to 0. In this case, all such values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the storage space for large objects.
Values of the
VARBINARY(MAX) columns can be stored outside of the actual data page. As you already know, KB is the maximum size of a row on a data page, but you can exceed this size limit if you use columns of such large data types. In this case, the system stores the values of these columns in extra pages, which are called row-overflow pages.
The storage in row-overflow pages is done only under certain circumstances. The primary factor is the length of the row: if the row needs more than 8060 bytes, some of the column’s values will be stored on overflow pages. (A value of a column cannot be split between the actual data page and a row-overflow page.)
As an example of how content of a table with large values is stored, Example 15.1 creates such a table and inserts a row into it.
USE sample; CREATE TABLE mytable (col1 VARCHAR(1000), col2 VARCHAR(3000), col3 VARCHAR(3000), col4 VARCHAR(3000)); INSERT INTO mytable SELECT REPLICATE('a', 1000), REPLICATE('b', 3000), REPLICATE('c', 3000), REPLICATE('d', 3000);Code language: SQL (Structured Query Language) (sql)
The CREATE TABLE statement in Example 15.1 creates the
mytable table. The subsequent INSERT statement inserts a new row in the table. The length of the inserted row is 10,000 bytes. For this reason, the row doesn’t fit in a page.
The query in Example 15.2 uses several catalog views to display information concerning page type description.
USE sample; SELECT rows, type_desc AS page_type, total_pages AS pages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE object_id = object_id('mytable');Code language: SQL (Structured Query Language) (sql)
The result is
In Example 15.2, the
sys.allocation_units catalog views are joined together to display the information in relation to the mytable table and the storage of its row(s). The
sys.partition view contains one row for each partition of each table or index. (Nonpartitioned tables, such as mytable, have only one partition unit.)
A set of pages of one particular data page type is called an allocation unit. Different allocation units can be displayed using the
type_desc column of the
sys.allocation_units catalog view. As you can see from the result of Example 15.2, for the single row of the
mytable table, two convenient pages plus two row-overflow pages are allocated (or reserved) by the system.
Note – The performance of a system can significantly degrade if your queries access many row-overflow data pages.
Parallel Processing o