Data Storage Internals - Pro SQL Server Internals - page 12

■ You can reduce the size of the data row by creating tables in the manner in which variable-length columns,
which usually store null values, are defined as the last ones in CREATE TABLE statement. This is the only case when the
order of columns in the CREATE TABLE statement matters.

Tip

The fixed-length data and internal attributes must fit into the 8,060 bytes available on the single data page. SQL
Server does not let you create the table when this is not the case. For example, the code in Listing 1-8 produces
an error.

Listing 1-8. Creating a table with a data row size that exceeds 8060 bytes

create table dbo.BadTable
(
Col1 char(4000),
Col2 char(4060)
) ;

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'BadTable' failed because the minimum row size would be 8067, including 7
bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Large Objects Storage
Even though the fixed-length data and the internal attributes of a row must fit into a single page, SQL Server can store
the variable-length data on different data pages. There are two different ways to store the data, depending on the
data type and length.

Row-Overflow Storage
SQL Server stores variable-length column data, which does not exceed 8,000 bytes, on special pages called
row-overflow pages . Let's create a table and populate it with the data shown in Listing 1-9.

Listing 1-9. ROW_OVERFLOW data: Creating a table

create table dbo.RowOverflow
(
ID int not null,
Col1 varchar(8000) null,
Col2 varchar(8000) null
);

insert into dbo.RowOverflow(ID, Col1, Col2) values (1,replicate('a',8000),replicate('b',8000));

As you see, SQL Server creates the table and inserts the data row without any errors, even though the data row
size exceeds 8,060 bytes. Let's look at the table page allocation using the DBCC IND command. The results are shown in
Figure 1-10 .