This will be a relatively short post simply explaining the way in which SQL Server stores databases records on disk. The reason that I’ve included this post is because I’ve come across a good few people who didn’t realise this was how SQL Server worked and yet this is an important precursor to my next few posts.
I’ll not be going into too much detail surrounding the makeup of the page, but will cover a few salient points which will hopefully clear up a little mystery surrounding SQL Server storage and reads. Therefore this is a post that I can see myself revisiting a few times, but I wanted the basics in here immediately to help with future posts.
So… how does SQL Server store your data on your hard drive?
I’ve spoken to a good few people who happily believe that SQL simply writes records to disk “as is”. As in, you create a record, SQL writes that record to the next free portion of the hard disk, wherever that may be. However, this is not the case at all.
There are a few intricacies I’m going to gloss over here (specifics around internals such as the slot array) but it will allow you to understand the concept of what’s happening internally.
Let’s say that you have a blank database and blank table… you want to add some data, so where does it go? Well, SQL Server creates 8k data pages within the data file and uses those to store your data.
Now, there are a couple of key points to note here and that is that SQL Server requires certain parts of the page to function as internal only. For example, references back to the table and database or a slot array which it uses to know where each record starts and in which order it’s being held.
Anyway, because of this internal usage (96 bytes per page), an 8k page (8192 bytes) has just 8096 bytes available for record data.
The next step is to look at how a record itself is stored in a page.
Basically a record is put together as follows:
Fixed Length Columns
Additional 1 bit per column in the table
Variable Length Offset:
Additional 2 bytes per variable column
Therefore if we had a table such as the following:
create table testRecord
id int identity(1, 1) not null,
randomString char(10) not null default('a'),
myVariable varchar(10) not null default('b'),
myDate datetime not null default(current_timestamp)
Then, by the following formula we could fit 218 records in each data page on disk…
4 bytes (header)
8 bytes + 10 bytes + 8 bytes (fixed length)
2 bytes + 1 byte (NULL bitmap… 4 columns, therefore 4 bits which uses 1 byte)
2 bytes + (2*1) bytes (Variable Offset)
= 37 bytes
8096 / 37 = 218 records per page.
Why does this all matter?
This will all become clear in my subsequent posts as although you may not realise it straight away, knowing this can make a vast difference to the way in which you view, design, and use SQL Server in the future.