SQL - Structured Query Language

Files and Filegroups

Database Files

SQL Server databases have three types of files. User Defined Database files information are stores in the database?s primary file and Master System Database.

Primary Data Files

Primary data file is a source of database that deploys with secondary data and log information files in the database. Every database must have one primary data file that file extension is .mdf.

Secondary Data Files

Secondary data files are additional resource of database that deploys with primary data and log information files in the database. A database may have zero, one or many secondary data files that file extension is .ndf. Secondary data files are used to spread data across multiple HDD to optimize memory structure and that helps to extended database when database exceeds the maximum size for a single window file.

Log Files

Log files are used to maintain database operation status that helps to recover original data when database operation should fail. Every database must have one or many log files that extension is .ldf.

Logical File Name

Logical file name is used to execute database operation on Physical File. Logical file name must comply with SQL Server Identifiers Rules and unique among logical file names in the database.

OS File Name

Operating System file name is used to define physical file path and file name. OS File Name must comply with Operating System File Name Rules.

Database Filegroups

SQL Server database objects and files can be grouped into filegroups to optimize physical storage structure of database and administration activities.

Primary Filegroups

Every database has a primary filegroup that contains System Tables, User Defined Primary and Secondary data files that not related with any user defined filegroups in the SQL Server Database.

User-defined Filegroups

User-defined filegroups are used to combine User Defined Secondary data files that help to optimize database operation. Log files are not possible to configure with filegroup. An instance, a simple data warehouse database estimation data volume is 100 GB that database can be create with one Primary (25 GB) data1.mdf data file and three Secondary (25 GB * 3) data2.ndf, data3.ndf and data4.ndf data files. These data files can deploy within one HDD or four different HDD to optimize physical storage structure of database.

Filegroup is used to combine different location?s secondary files that help to execute a Query among different resource disks. This concept can accomplished by using a single file created on a RAID (Redundant Array or Independent Disks) stripe set. However, new data files and filegroups can add or modify in the specified disks through ALTER DATABASE.

* * * * *

Your Comment To AUTHOR