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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1427 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 (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859666 Visits: 48576
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1427 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 (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859666 Visits: 48576
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 (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4611 Visits: 917
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