What happens when you issue CREATE DATABASE command in SQL Server?

Chronicles of SQL Server's Storage Internals - Part 1

What happens when you issue a "CREATE DATABASE [FooBar]" statement?

Turns out that TWO files get created - FooBar.mdf and FooBar.ldf. And that's it! Two binary files that store EVERYTHING that you will ever do with your database!

FooBar.mdf is the more interesting one. MDF extension stands for "Main Data File". And it contains exactly what the name implies - EVERYTHING related to your [FooBar] database!

FooBar.ldf is much simpler. LDF extension stands for "Log Data File" and it represents a raw list of all WRITE commands that you ever issue against your Database! This is something that we will cover in more depth in one of the future articles.

Good question to ask is - what is REALLY contained inside .MDF?

It shouldn't come as a surprise that it's a Binary File, but what is stored inside? And, why do Docs mention stuff like Auto-Grow, Shrink, etc.?

You can think of .MDF file as if it was a Book. Question becomes - WHAT IS a BOOK?

At the most fundamental level -- Book is nothing more but organized collection of PAGES. As simple as that.

What differentiates the Pages is the CONTENT. As in - what is WRITTEN on the page.

As such, most of the pages contain text and images, but there are 'special' ones that contain stuff like Table of Contents, Index of Words used, list of References, etc.

.MDF file is a Book! And, surprise surprise, it's made of PAGES!

These are DIGITAL pages (binary, if you will!) and their size is measured in bytes. Interestingly though, unlike regular books, size of these virtual pages is ALWAYS 8KB. Always. I guess we could say it's a bit more standardized than your printed book!

In the next article we will go into details of WHAT Pages are, how they look like, etc.

What is important to remember for now is that .MDF file contains PAGES. A bunch of them (by default, 8000 of them get created when you cast a new database). And all the data you will ever have in your DB is spread over these digital pages! It's really simple! :)

NOTE: If you are concerned about I/O bottleneck of a single file - fear not! There's a concept called FILEGROUPS that lets you split the .mdf into smaller chunks (usually named .ndf files) and spread them over multiple locations!