How data exceeding 8 KB stores in a row

Maximum allowed bytes per row is 8060 bytes in SQL Server. There are two ways to store data rows that exceed this limit.

• LOB data
• ROW_OVERFLOW_DATA

Let’s see how this works in SQL server with a simple example.

• Create the below table

CREATE TABLE DataTest (ID int,

                                  Location varchar(MAX),

                                         FirstName varchar(5000),

                                         LastName varchar(4000),

                                         Region varchar(4500))

•      Then insert a record to the table with the below query

INSERT INTO DataTest

VALUES (1,'', REPLICATE('A',5000), REPLICATE('B',4000), REPLICATE('B',4500))

According to the sizes of the above columns neither of two columns can be fit in to a single data page. So only one column will fit into the row and other columns will be saved in different pages. This is called ROW_OVERFLOW_DATA

 This can be observed with the below query

SELECT alloc_unit_type_desc

,index_depth

,index_level

,record_count

,page_count

,avg_record_size_in_bytes

FROM sys.dm_db_index_physical_stats(DB_ID('MyTestDB'),OBJECT_ID('DataTest'),NULL,NULL,'DETAILED')

Results are …

In the column “page_count”, there is one page with IN_ROW_DATA and two pages with ROW_OVERFLOW_DATA. As I explained above, data the exceeds 8 KB, will be stored in different pages as ROW_OVERFLOW_DATA.

•        Now let’s insert another record that contains a value for varchar(MAX) column with below query

INSERT INTO DataTest

VALUES (2,REPLICATE('A',8000),'','','')

Now the data pages looks as below

Now we can see that the page_count of IN_ROW_DATA has been increased by one. This means that the new row has been inserted as a IN_ROW_DATA since the record fits into the limit of 8 KB.

    
       Let’s insert another record which exceeds 8 KB with the below query

INSERT INTO DataTest

VALUES (2,REPLICATE('A',8000),REPLICATE('A',5000),'','')

Now the result looks like,

An additional page has been added with the allocation unit type “LOB_DATA”.