Database storage strategies

- [Instructor] When creating your databases in SQL Server, you should consider a few aspects of the storage for the files that make up your databases. In order to make informed decisions, it's important to understand the file system that SQL server uses. SQL users two basic file types known as a data file and a log file, and the data file is where all of your information gets stored, and it could be in a primary or a secondary data file. And we will see that the primary file in the file system has an MDF extension and the secondary has an NDF extension.

But we also have the log file that SQL Server uses as well, and this is where all of your transactions get written to before they actually make it into the database itself. You can also create an additional file known as a secondary data file which will have a dot in the f extension. This allows you to increase the size of your database. So secondary files are user defined, and they're often used to spread your data across disk subsystems or to add more space to a database when your primary file is starting to fill up.

It's also important to note that they do not store system data. The data files that make up your database may contain data, database tables, and database objects, so as you create tables within your database to store the data, your data files will contain those tables. Database objects are things such as stored procedures and functions that you might create to help the database function within the organization. Every database has a primary filegroup, and this filegroup contains your primary data file and any secondary files that you don't put into other filegroups.

Your user-defined filegroups can be created to group data files together for administrative, data allocation, and for placement purposes. When objects are created in the database without specifying which filegroup they belong to, they are assigned to the default filegroup, and any time exactly one filegroup is designated as the default filegroup, the files in the default filegroup must be large enough to hold any database objects that are not allocated to other filegroups.

The primary filegroup is your default filegroup unless it is changed by using an alter database statement. Allocation for the system objects and tables remains within the primary filegroup, not your new default filegroup. Your default locations, and in this case here, are stored in the path that we see in the middle section, so they're stored in our database folder for the SQL Server. So in summary, understanding how SQL server stores the data in the different file types on your file system helps you to ensure that your configuration of the storage location and the allocation of the space for your database files will not impact your system negatively.