Database Reference
In-Depth Information
Chapter 1
Data Storage Internals
SQL Server database is a collection of objects that allow you to store and manipulate data. In theory, SQL Server
supports 32,767 databases per instance, although the typical installation usually has only several databases.
Obviously, the number of the databases SQL Server can handle depends on the load and hardware. It is not unusual
to see servers hosting dozens or even hundreds of small databases.
In this chapter, we will discuss the internal structure of the databases, and will cover how SQL Server stores
the data.
Database Files and Filegroups
Every database consists of one or more transaction log files and one or more data files. A
transaction log
stores
information about database transactions and all of the data modifications made in each session. Every time the data is
modified, SQL Server stores enough information in the transaction log to undo (rollback) or redo (replay) this action.
■
Note
We will talk about the transaction log in greater detail in Part 6 of this topic “Inside the Transaction Log.”
Every database has one primary data file, which by convention has an .mdf extension. In addition, every database
can also have secondary database files. Those files, by convention, have .ndf extensions.
All database files are grouped into the filegroups. A
filegroup
is a logical unit that simplifies database
administration. It permits the logical separation of database objects and physical database files. When you create
database objects-tables, for example, you specify into what filegroup they should be placed without worrying about
the underlying data files' configuration.
Listing 1-1 shows the script that creates a database with name
OrderEntryDb
. This database consists of three
filegroups. The primary filegroup has one data file stored on the
M:
drive. The second filegroup,
Entities
, has
one data file stored on the
N:
drive. The last filegroup,
Orders
, has two data files stored on the
O:
and
P:
drives. Finally,
there is a transaction log file stored on the
L:
drive.
Listing 1-1.
Creating a database
create database [OrderEntryDb] on
primary
(name = N'OrderEntryDb', filename = N'm:\OEDb.mdf'),
filegroup [Entities]
(name = N'OrderEntry_Entities_F1', filename = N'n:\OEEntities_F1.ndf'),
filegroup [Orders]
(name = N'OrderEntry_Orders_F1', filename = N'o:\OEOrders_F1.ndf'),
