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

Improve this question
4
  • Read this please :sqlskills.com/blogs/paul/… ; sqlservercentral.com/blogs/practicalsqldba/2012/08/22/…
    – Sabin B
    Commented Apr 21, 2017 at 8:16
  • 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)
    – Sabin B
    Commented Apr 21, 2017 at 9:02
  • 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.
    – info.sqldba
    Commented Apr 21, 2017 at 9:12
  • Try to read , from Paul Randal, sqlskills.com/blogs/paul/… His article is more precise and he is well inform
    – Sabin B
    Commented Apr 21, 2017 at 9:21

2 Answers 2

Reset to default
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
Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.