FileGroups & Partitions

Chronicles of SQL Server's Storage Internals - Part 10

The very first post introduced concept of .MDF being Main Database File. But there are also .NDF files. And these are the ones you get by defining FileGroups.

(NOTE: For the hell of it, I couldn't find the etymology of .NDF extension. /u/SQL_Guy on Reddit claims it stands for 'Nother Data File)

FileGroups are an easy way to do Load Balancing of your data. Just like you wouldn't put all eggs in the same basket, and for the same reason you usually have multiple HTTP servers. It's in order to split the load and logically organize your data :)

(NOTE: If you are using SSDs all the way than this is not as as dramatic as it was with mechanical drives!)

Content inside .NDF files is exactly the same as in .MDF. It contains Data Pages, Allocation Maps, Indexes, etc. The only difference is that YOU are the one who specifies what goes where :)

Similar is true for Partitions. If FileGroups allow you to Balance the Load on Database-level, Partitions allow you to do the same on single table level. But everything else is pretty much the same, really.

All in all - two quite simple and powerful concepts!

Next article will start digging a bit more into WHYs behind Indexes and how they work. Stay tuned!