SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How does a integer/any data record store in SQL Server page


How does a integer/any data record store in SQL Server page

Author
Message
info.sqldbamail
info.sqldbamail
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 270
Hello Experts,

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 below images:



---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!

When life puts into problem don't say 'God Why Me' sayw00t 'Try Me'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87595 Visits: 45272
Rows have headers as well. Dump type 3 on DBCC Page will shred the row as well, so you can see what's there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


info.sqldbamail
info.sqldbamail
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 270
I Have seen the information from DBCC PAGE where i am not have inforamation below structure of

1 Byte for STATUS BITS1 + 1 Byte for STATUS BITS2 + 2 Bytes for Fixed length + 4 Bytes of Fixed length of data(Column ID) + 2 Bytes for all columns + 1 Byte for null bitmap.

I don't know what are these status bits and bit null map and why is it storing columns info in 2 bytes.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!

When life puts into problem don't say 'God Why Me' sayw00t 'Try Me'
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87595 Visits: 45272
Sounds about right. Those two "2 Bytes for Fixed length" and "2 Bytes for all columns" are storing information about the offsets within the data portion for the fixed length columns (the first) and the variable-length columns (the second)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search