How does a integer data record store in SQL Server page

Question

I have seen the 8KB (8192 = 96 Header + 36 Row Offset + 8060 Free space) page architecture in SQL Server. When it comes to storing a data record in Page i am confused.

In Below table i have create Integer for column ID it should take 4 Bytes but each record size/length is showing in DBCC PAGE Command 11 Bytes.

I have created a simple table as below:

CREATE TABLE PAGETEST
(
 ID int primary key
)
GO
INSERT INTO PAGETEST values (200) 

Q 1). Why a simple integer data is taking 4 actual bytes of data + 7 Bytes extra = 11 Bytes.
Q 2). Can anybody explain how does a record store in a page.

Kindly find the images below:

enter image description here

• Sabin B: Read this please :sqlskills.com/blogs/paul/… ; sqlservercentral.com/blogs/practicalsqldba/2012/08/22/…
• Sabin B: I believe size 11 is because : 1 byte (for status bits 1 )+ 1 byte (for status bits 2 )+ 2 bytes (used to store the size of fixed length columns)+ 4 bytes (of fixed length column ID )+ 2 bytes (used to store total number of columns)+ 1 byte (for null bitmap)
• info.sqldba: Hello Sabin, Can you please explain in breif what are these status bits and null bitmap and why it is storing 2 bytes for total columns and how.
• Sabin B: Try to read , from Paul Randal, sqlskills.com/blogs/paul/… His article is more precise and he is well inform

Answer 1

One important thing to keep in mind is that rowstore tables have a minimum row size of 9 bytes. You can see some details about that in the answer and comments here. If you're going to be creating sample data and digging around in pages I recommend creating at least a few columns to make what you're looking at more clear. Otherwise you can run into a case where a table with a single TINYINT column appears to take up as much space as a table with a single SMALLINT, which doesn't make sense at first.

CREATE TABLE dbo.X_TINYINT (ID SMALLINT NOT NULL);
CREATE TABLE dbo.X_SMALLINT (ID SMALLINT NOT NULL);
INSERT INTO dbo.X_TINYINT WITH (TABLOCK)
SELECT TOP (100000) 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
INSERT INTO dbo.X_SMALLINT WITH (TABLOCK)
SELECT * FROM dbo.X_TINYINT;
sp_spaceused 'X_TINYINT'; -- 1096 KB reserved
sp_spaceused 'X_SMALLINT'; -- 1096 KB reserved

Answer 2

This is explained better in this article as mentioned by @sabin bio. https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/

In your page dump if you look at hex value of c8 it turns out to be 200 in decimal. That is the data you inserted.
c8000000 takes 4 bytes and that is fixed length data. 4 (1000800) and 3 (01000) bytes before and after fixed length data are explained in Paul's blog post.