How does a integer/any data record store in SQL Server page
-
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' say:w00t: 'Try Me'
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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. - The master data required for page management also comprises that extra space . Like the Offset , IAM info etc.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply