0

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

Scott M
  • 356
  • 2
  • 10
info.sqldba
  • 327
  • 4
  • 15

2 Answers2

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
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
0

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.

SqlWorldWide
  • 13,687
  • 3
  • 30
  • 54