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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1461 Visits: 278
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 (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)

Group: General Forum Members
Points: 903740 Visits: 48756
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1461 Visits: 278
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 (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)

Group: General Forum Members
Points: 903740 Visits: 48756
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


davidmsdw
davidmsdw
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 21
The page structure is quite complex, the below links will cover a lot of it, but does not include things like In-memory OLTP structures.

http://www.sqlservercentral.com/blogs/practicalsqldba/2012/08/12/sql-server-understanding-the-data-page-structure/ - "SQL Server: Understanding the Data Page Structure"

Paul Randall knows a LOT about storage as he did a LOT of work on DBCC in the 2005 timeframe:

https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/ - "Inside the Storage Engine: Anatomy of a page"

https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/ - "Inside the Storage Engine: Anatomy of a record"

Also found this recently which covers items you might not consider:

http://rusanu.com/2011/10/20/sql-server-table-columns-under-the-hood/ - "SQL Server table columns under the hood"

Regards,
David.
Arsh
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5095 Visits: 998
The master data required for page management also comprises that extra space . Like the Offset , IAM info etc.

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