Before going into details let us see how this looks internally in SQL server. Let us create a table and insert some records into it.
GOUSE MyDb
GOCREATE TABLE Customer (
FirstName CHAR(200),
LastName CHAR(300),
Email CHAR(200),
DOB DATE,
)GOINSERT INTO Customer VALUES('William','James','William.J@yahoo.com','1982-01-20')INSERT INTO Customer VALUES('Jade','Victor','Jade.V@yahoo.com','1985-08-12')INSERT INTO Customer VALUES('Jonas','hector','Jonas.h@yahoo.com','1980-10-02')
INSERT INTO Customer VALUES('William1','James','William.J@yahoo.com','1982-01-20')
INSERT INTO Customer VALUES('Jade1','Victor','Jade.V@yahoo.com','1985-08-12')INSERT INTO Customer VALUES('Jonas1','hector','Jonas.h@yahoo.com','1980-10-02')INSERT INTO Customer VALUES('William2','James','William.J@yahoo.com','1982-01-20')INSERT INTO Customer VALUES('Jade2','Victor','Jade.V@yahoo.com','1985-08-12')INSERT INTO Customer VALUES('Jonas2','hector','Jonas.h@yahoo.com','1980-10-02')INSERT INTO Customer VALUES('William3','James','William.J@yahoo.com','1982-01-20')GO
Now we need to find out the pages allocated to this table. For that we have to use an undocumented command DBCC IND.
DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 });
nonclustered indid = non-clustered Index ID
1 = Clustered Index ID
0 = Displays information in-row data pages and in-row IAM pages (from Heap)
-1 = Displays information for all pages of all indexes including LOB (Large object binary) pages and row-overflow pages
-2 = Displays information for all IAM pages
Run the below command from SSMS
DBCC IND('mydb','customer',-1)
The output will looks like as in below picture:
You can see two records, one with page type 10 and other one with 1. Page type 10 is an IAM page and we will talk about different page types in a different post.Page type 1 is data page and its page id is 114.
Now to see the row data stored in that page , we have to use the DBCC PAGE command. The syntax of DBCC PAGE :
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);Printopt:
0 - print just the page header
1 - page header plus per-row hex dumps and a dump of the page slot array
2 - page header plus whole page hex dump
3 - page header plus detailed per-row interpretation
DBCC TRACEON(3604)GODBCC page('mydb',1,114,3)
This will have four section in output.The first section is BUFFER which talk about in memory allocation and we are not interested in that section. The next section is page header which is fixed 96 bytes in size.The size of page header will be same for all pages. Page header section will looks like as below picture.
To know more about these field http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx
(8*1024)- 96-(10 * 703)-(10*7)-(10*2)
where 8*1024 = Total number of bytes in the page
96 = Size of Page Header
10*703 = Number of records * size of four columns in the table
10*7 = Number of records * row overhead
10*2 = Number of records * size in bytes to store the row offset table
Now we have seen the structure of the page. Let us summarize this . A page is 8KB size. That means 8192 bytes. Out of these, 96 bytes are used for page header which is in fixed size for all data pages. Below that, data records are stored in slots.The maximum length of data records is 8060 bytes. This 8060 include the 7 bytes row overhead also . So in a record you can have maximum of 8053 bytes. The below create table statement will fail.
CREATE TABLE Maxsize(
id CHAR(8000) NOT NULL,id1 CHAR(54) NOT NULL
)
Msg 1701, Level 16, State 1, Line 1Creating or altering table 'Maxsize' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
Reference:I have learned about the page structure from Paul Randal excellent post on this subject.
If you liked this post, do like my page on FaceBook
