1. Back To Blog

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

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.

 

sqlserver

 

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.

sqlserver

 

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).

 

See this mind blowing SQL Server interview question video: - What are inner joins left join and right join?

 

 

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

 

Regards,

 

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

Shiv Prasad Koirala

Visit us @ www.questpond.com or call us at 022-66752917... read more

We are on Social