I was reading something about SQL Server and one line particularly caught my attention “It’s Ironic that the Relational Database Management System (RDBMS) was invented to implement data structures in something other than files. The irony is that even though the RDBMS doesn’t work with independent text files, files are still at the heart of the engine.” Interesting isn’t it. I am sure very few of us thought in this way although many of us must be knowing about the File and Filegroup systems. Incase you’re not sure about the difference between a file and a filegroup : A data file is an Operating system binary files in which SQL Server stores the contents of the database (e.g., data, indexes, metadata etc.) on the physical storage medium like your computer’s hard drive, and a file group is a logical container for managing and grouping of one or more data files.
Filegroups are named collections of files and are used to help with the data placement and administrative tasks such as backup and restore operations.
- Primary Data Files: Every database has one primary data files. And the recommended file extension for the primary data file is .mdf
- Secondary Data Files: Secondary data file consists of all the files other than the primary data file. The recommended file extension for the secondary data file is .ndf . It is possible that some of the database may not have any secondary data files while others have several secondary files.
- Log files: Log file hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one log file depending on the size and users of the database. The recommended file extension for the log file is .ldf
Whenever, we do any insertion,updation or deletion of records in the database tables, then the changes are stored in the transaction log first, and after sometime a process is started which records all the changes from the transaction log to the database files. SQL Server works with the files by assigning the files to Filegroups. Even if the database has only one file, it will still be placed in the Filegroup.
Lets try to explain the above point through a simple example:
–SIMPLY CREATING THE DATABASE
CREATE DATABASE MY_FIRST_DATABASE
- MY_FIRST_DATABASE whose groupid is 1 (Name same as Database created)
- MY_FIRST_DATABASE_log whose groupid is 0.
Now lets check what is the name of the group whose groupid is 1:
Interesting I already have a Filegroup, but I did not create it. SQL Server created it for me. It is the default Filegroup. Only filegroup can be default filegroup at a time.Now if I create any tables or any other objects then they will be stored here. I can surely change it later on if I want.
Have you noticed one thing? The group id of the of the MY_FIRST_DATABASE_log file is 0, which means it is not in the PRIMARY Filegroup. Yes, you guessed it right only the database file are contained in the Filegroup, log files do not reside in the Filegroup. Log space is managed separately from data space.
SQL Server data and log files can be place on either FAT or NTFS file system, but cannot be placed on compressed file systems .Some systems can improve their performance by controlling the placement of data and indexes onto specific disk drives. Filegroups can help in this process. The system administrator can create filegroups for each disk drives and then assign specific tables, indexes, or the text, ntext or image data from the table, to specific filegroups.
Note: A maximum of 32,767 file and 32,767 filegroups can be specified for a database.
Aren’t you tired of reading?? At least I am tired of writing now..will continue more about File and Filegroup system in my next blogs..till then relax, enjoy and keep learning!!!