Sql server® 2012 t-sql fundamentals

FIGuRE 1-7

  Database layout. 

The database is made up of data files and transaction log files. When you create a database, you 

can define various properties for each file, including the file name, location, initial size, maximum size, 

and an autogrowth increment. Each database must have at least one data file and at least one log file 

(the default in SQL Server). The data files hold object data, and the log files hold information that SQL 

Server needs to maintain transactions. 

www.it-ebooks.info

---

18  

Microsoft SQL Server 2012 T-SQL Fundamentals

Although SQL Server can write to multiple data files in parallel, it can write to only one log file at a 

time, in a sequential manner. Therefore, unlike with data files, having multiple log files does not result 

in a performance benefit. You might need to add log files if the disk drive where the log resides runs 

out of space.

Data files are organized in logical groups called filegroups. A filegroup is the target for creating an 

object, such as a table or an index. The object data will be spread across the files that belong to the 

target filegroup. Filegroups are your way of controlling the physical locations of your objects. A data-

base must have at least one filegroup called PRIMARY, and can optionally have other user filegroups 

as well. The PRIMARY filegroup contains the primary data file (which has an .mdf extension) for the 

database, and the database’s system catalog. You can optionally add secondary data files (which have 

an .ndf extension) to PRIMARY. User filegroups contain only secondary data files. You can decide 

which filegroup is marked as the default filegroup. Objects are created in the default filegroup when 

the object creation statement does not explicitly specify a different target filegroup.