Database Pages: The Building Blocks of Your Data
A database page is the fundamental 8KB block for all storage. The database engine reads and writes entire pages, not single rows, for user data, indexes, and metadata. The key footgun: the physical order of rows on a page is not guaranteed.
Why it exists
Databases need an efficient way to manage data on disk. Reading and writing tiny, individual rows would be incredibly slow due to disk seek times. Grouping data into fixed-size blocks, or pages, allows the database to perform I/O in larger, more efficient chunks, minimizing the number of slow disk operations.
The mental model
A database page is the fundamental unit of data storage, typically an 8KB block of disk space. Think of your database file as a large book. Every piece of information, whether it's the story itself (data rows), the table of contents (metadata), or the index at the back (index structures), is written on these standard-sized pages. The database engine always reads or writes one full page at a time.
How it works
Every page begins with a 96-byte header containing metadata like the page number and type. Data rows are stored after the header. At the very end of the page, a structure called the 'slot array' grows backwards. This array contains 2-byte pointers (offsets) to the location of each row on the page. This design allows the database to maintain the logical order of rows (e.g., by index key) even if the physical storage on the page becomes fragmented after deletes and updates. When a row is deleted, its space can be reused by a new row.
When to use it
This isn't a feature you choose to use; it's a core concept of how relational database engines like SQL Server work. Understanding pages helps you reason about performance, fragmentation, and storage. For example, choosing data types wisely affects how many rows fit on a page, which directly impacts I/O efficiency and query speed.
When not to use it
The concept doesn't apply to all storage systems. For example, transaction log files in SQL Server are not page-based; they are a continuous stream of log records. Some NoSQL or key-value stores may use different storage abstractions entirely.
One canonical example
A page can be a Data Page, holding user table rows. Or it can be an Index Page, holding B-Tree nodes that help locate those rows. Other types include Text/LOB pages for large objects (like varchar(max) or xml) and system pages like GAM (Global Allocation Map) that track which groups of pages are in use.
Interview question
What is the primary reason database engines utilize fixed-size pages for data storage?
- a.To guarantee the physical order of rows on disk for faster sequential access.
- b.To optimize disk I/O by performing fewer, larger read and write operations.Correct
- c.To simplify the process of encrypting data blocks for enhanced security.
- d.To enable dynamic resizing of individual rows within a table without fragmentation.
Why? this is the answer
The card states that pages exist to "minimize the number of slow disk operations" by grouping data into "larger, more efficient chunks" for I/O. Option A is incorrect because the card explicitly mentions "the physical order of rows on a page is not guaranteed."
Just read this? Test yourself on what you have been reading.
Read the original → learn.microsoft.com
- #databases
- #storage
- #architecture
- #sql server
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles