Special Storage Series – I (Checking the 8K (8192) size of page)

There are pages for in-row data, page for row over flow data and pages for LOB data, but in SQL Server all data pages have a fixed size of 8K (8192 bytes). They consist of:

• Page Header
• Data Rows
• Row offset Array

Page Header: It consist of first 96 bytes of each page (thus leaving 8096 byte for data rows, row over head and row offset arrays)

Data Row: This the area where the actual table data gets stored, the maximum size of singe data row is 8060 bytes of a in-row data. The rows can have row-overflow and LOB Data stored on separate page.

Row offset Array:  It is a block of 2 byte entries, indicate the offset at which the corresponding data row begins. Every row has 2 byte entries in this array. The row offset array indicates the logical order of rows on a page in case of Clustered Index, this doesn’t mean rows are physically stored in the page in the order of cluster index key.

Now we will try to check the maximum size of table structure which we can create in SQL Server 2008, as we know the maximum size of data row is 8060, we will try to create table of this length and introspect it

create table Tab_VKM_SpecialStorageCheck

(col1 char(3000),

col2 char(3000),

col3 char(2000),

col4 char(60))

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'Tab_VKM_SpecialStorageCheck' 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.

This shows that the maximum row size values includes several bytes overheads stored with the row so the total size of the tables defined columns must be slightly less than this amount (8060 bytes)

Programmer can’t stand out without programming  

Post Reference: Vikram Aristocratic Elfin Share