How data is stored

Every database on SQL Server must have at least two files:

• The primary data file with the usual suffix, mdf
• The transaction log file with the usual suffix, ldf

For lots of databases, this minimal set of files is not enough. When the database contains big amounts of data such as historical tables, or the database has big data contention such as production tracking systems, it's good practise to design more data files. Another situation when a basic set of files is not sufficient can arise when documents or pictures would be saved along with relational data. However, SQL Server still is able to store all of our data in the basic file set, but it can lead to a performance bottlenecks and management issues. That's why we need to know all possible storage types useful for different scenarios of deployment. A complete structure of files is depicted in the following image:

For many administrators who never care about databases on SQL Server before, this tree of objects could be unclear and confusing. It's time to explain every single node in the preceding tree.