SQL Row Allocations

In this article we will try to understand the types SQL row allocations.

In sql the data is stored in pages, in row format. The max size of SQL page is 8kb (8192 bytes). If we try to create a data row more than 8060 bytes or a column more than 8000 bytes, sql will throw error. It’s because of the sql default behaviour, sql tries to keep the entire data row in to a single page rather extending page. Let’s prove this with small demo.

Case 1: Max Column Size 8000 Bytes

Create table Page_Practice(Name char(9000))

Case 2: Max data row size 8060 Bytes

Create table Page_Practice(Firstname char(800),LastName char(8000))

Then you may getting question what if want to store bigger data we need to use “VARCHAR, VARCHAR(MAX)”. Let’s deep dive more on these.

SQL Row Allocation Types :

In Sql there are three types of row allocations

• In-Row Data
• Row-Overflow Data
• Lob Data

In-Row Data

SQL server initially assign In-Row Data for all types of data pages. The data row size should be with in 8060 bytes.

Create table Page_Practice(FirstName varchar(100), LastName varchar(100))
GO
Insert into Page_Practice values ('Maheswar','Reddy')
Go
DBCC IND('Test',--database name
Page_Practice,--table name
1)

Note: All the page allocation types(iam_chain-type) are in-row data.

Row-Overflow Data

Once the data row size exceeding 8060 bytes, sql server will extend the sql page by creating Row-Overflow Data.

Create table Page_Practice(Name char(8000),
Firstname varchar(300),
LastName varchar(8000))

If you notice the above query asking for min-meory as 5000 bytes and max-memory as 10000 bytes where as single SQL page is 8192 bytes, so sql has to extend the page. So when we create table we get a warning like following.

Insert into Page_Practice values ('Maheswar','M',REPLICATE ('Reddy', 500))
GO
DBCC IND('Test',--database name
Page_Practice,--table name
1)

Note : Sql will extend the row information to Row-overflow data pages. Check the data in row-overflow data pages by

DBCC TraceOn(3604)
DBCC PAGE('Test' --database name
, 1 -- file ID
, 24808--Page ID
, 1 -- Print mode = 3 displays header and row information        
);

In the above screen shot if we notice only the last name stored in extended page (row-overflow data), other data is stored in “In-row data” pages.

Lob Data

If we want to store greater than 8000 bytes in column we need to use VARCHAR(MAX). Then SQL server will assign Lob Data when we use VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX).

Create table Page_Practice(Name varchar(max),
Firstname varchar(300),
LastName varchar(8000))

GO

Insert into Page_Practice values (
REPLICATE(CONVERT(VARCHAR(MAX),'Maheswar'),3000),
'M',
REPLICATE ('Reddy', 500))

GO

DBCC IND('Test',--database name
Page_Practice,--table name
1)

NOTE : If you notice in above query, we are using convert to varchar(max) as replicate is by default max 8000 char. To generate more than 8000 char we need to use following code .

“REPLICATE(CONVERT(VARCHAR(MAX),’Maheswar’),3000)”

DBCC TraceOn(3604)
DBCC PAGE('Test' --database name
, 1 -- file ID
, 8096--Page ID
, 1 -- Print mode = 3 displays header and row information        
);

I think i made SQL storage types are very clear. Thanks for reading.