Tuesday, January 8, 2013

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
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
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
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
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
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”.