1. Back To Blog

SQL Server interview questions: - What are page, extents, page header and row offset in SQL Server?

• By Shiv Prasad Koirala in SQL
• Aug 16th, 2012
• 12285
• 0

This note is taken from SQL Server interview question by Shivprasad Koirala by BPB publication. You can buy the book from clicking on buy SQL Server interview questions

Extent and pages define how data is stored in SQL Server. The actual data is stored in pages. Each one of these pages are of 8 KB size. You can also visualize pages as the fundamental unit to store data. In other words your table row data gets stored actually in pages.

Pages are further grouped in to extent. One extent is collection of eight pages. So the size of each extent is 64 KB (i.e. 8 pages x 8 KB size).

Finally these extents sum up in to the MDF physical file which you see on your hard disk.

 

 

Now in reality the complete 8 KB size is not allocated only to pure end user data.

Page is divided in to 3 sections Page header, Data row and row offset, see the below figure for visual's.

 

Page header stored system information about the page. After the page header data row is stored serially. Row offset information is stored at the end of the page, after the data rows. Every data row has a row offset. Row offset stores information about how far the row is from the start of the page.

Putting in simple words the complete page equation comes as shown below.

Page (8 KB/8192 bytes) = Page header (96 bytes) + Actual data (8060 bytes) + Row offset (36 bytes).

Do not forget to look in to .NET, c#, SQL Server video product.

Here is our latest 20+ SQL Interview Questions and Answers video :-

Click to view more from author's on SQL Server Interview question