Data Page Restrictions

Today I’m talking about limitations that you have with data pages, and why there are restrictions that you will love, while you will hate other restrictions. As you learned, a data page is always 8kb large, and you can store 8060 bytes of data on it. And the size of your records dictates how many records you can store on one page. When you deal with fixed-length data types (like CHAR, INT, DATETIME, etc.) you have the restriction that the record length can’t exceed the 8060 bytes including the internal overhead that SQL Server uses.

Restrictions – the good ones

When you have a table with less than 8 columns, you need to add 7 bytes for the internal overhead. And for every 8 more columns you must add 1 additional byte, e.g., with 17 columns you need 9 bytes internal overhead (7 + 1 + 1). If you are trying to create a larger record size, SQL Server will return an error message during your CREATE TABLE statement. Just have a look at the following table definition.

CREATE TABLE TooLargeTable1
(
   Column1 CHAR(5000),
   Column2 CHAR(3000),
   Column3 CHAR(54)
)
GO

As you can see, every record need 8061 bytes (5000 + 3000 + 54 + 7 bytes). In that case, SQL Server returns the following error message when you try to create the table:

When you create a table with more than 8 columns, you must consider that now SQL Server needs 8 additional bytes of overhead:

CREATE TABLE TooLargeTable2
(
   Column1 CHAR(1000) NOT NULL,
   Column2 CHAR(1000) NOT NULL,
   Column3 CHAR(1000) NOT NULL,
   Column4 CHAR(1000) NOT NULL,
   Column5 CHAR(1000) NOT NULL,
   Column6 CHAR(1000) NOT NULL,
   Column7 CHAR(1000) NOT NULL,
   Column8 CHAR(1000) NOT NULL,
   Column9 CHAR(53) NOT NULL
)
GO

This is again an invalid table definition (8000 + 53 + 8 bytes), where SQL Server will return an error message.

Restrictions – the bad ones

In the previous section I have shown you data page restrictions that you will love, because SQL Server returns an error message when you try to create the table. But there are also data page restrictions that you will hate, because SQL Server will allow you to create the table, and sometimes INSERT statements will succeed, and sometimes they will just fail… Let’s have a look at that.

The problem that we are facing here is with variable-length data types, like VARCHAR. When these columns don’t fit into the data page itself, SQL Server is able to move them to an off-row location on a separate page. This is a so-called Row-Overflow Page. On the original page SQL Server leaves a pointer that is 24 bytes long that points to the row-overflow page. And in some cases, this pointer can exceed in combination with the other columns the limitation of the 8060 bytes. Let’s have a look at the following table definition.

CREATE TABLE TooLargeTable3
(
   Column1 CHAR(5000),
   Column2 CHAR(3000),
   Column3 CHAR(30),
   Column4 VARCHAR(3000)
)
GO

As you can see I’m using here a VARCHAR(3000) data type. SQL Server will give you here also a warning, as you can see. The warning means that you can create the table, but INSERT/UPDATE statements may fail…

The following insert in the table will succeed:

INSERT INTO TooLargeTable3
VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 19)
)
GO

The record size is here 8056 bytes long (5000 + 3000 + 30 + 19 + 7 bytes). In this scenario SQL Server will also store the data of the 4th column directly on the main data page. But imagine now the following INSERT statement.

INSERT INTO TooLargeTable3
VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 3000)
)
GO

In the previous INSERT statement, SQL Server must move the data of the 4th column to a row-overflow page, because the 3000 bytes can’t fit in any way into the main data page. This also means that SQL Server will leave here a pointer of 245 bytes that points to the different page where the data can be found. Therefore, our record size is now 8061 bytes long (5000 + 3000 + 30 + 24 + 7 bytes).

Boom, your record size is larger than 8060 bytes, and the INSERT statement fails!

These are the bad restrictions, because they are hitting you during the operation of your database, the good ones are visible up-front when you define the table schema. Just think about that…

Summary

When you are designing your table schema, you should really think very carefully what you are doing. As you can see there are plenty of restrictions that you can run into when you deal with data pages in SQL Server. Of course, when SQL Server gives you an error message, and you are not allowed to create a table, everything is fine (almost).

But when you get warnings, mostly everyone just ignores them without thinking about them. That’s always a bad practice, because as you have seen here, your INSERT may fail at runtime, so you can’t predict when problems will arise. I hope that I have given you today a good insight why the understanding of the internal structure of data pages is essential.

Thanks for your time,

-Klaus