SQL Server 2000 Administration in 15 Minutes a Week: Database Creation Basics


Welcome to the fifth article in my series SQL Server Administration in 15
Minutes a Week. Last week we took a look at the SQL Server Enterprise Manager.
We learned how to register our SQL Servers, how to organize Servers into groups,
and we learned about the databases that are installed by default. This week we
are going to learn about the files that make up a database and we will also
create our first database. The topics for this article include:

– Database Files
– Database Filegroups
– Creating a Database


Database Files

Databases
in SQL Server 2000 have an underlying data storage structure made up of two or
more system files. Each database must have at least one data file, used for
storing database objects (tables, views, stored procedures, etc), and one log
file, used for storing transaction information. In addition, each database must
have its own files; you can’t share files between databases. 

Data
files store objects and the data inside objects. The first data file you create
is known as the “Primary data file.” If you need to create additional data files
for your database, they must be created as “Secondary data files.”  

Log
files, on the other hand, are used to track changes in the database and have
only one file type. In the event of a system failure, the log files can be used
to repair the database. We will look at how SQL Server tracks database
modifications when we start talking about transactions. 

The
following table summarizes the three file types. 

File
Type

Description

Primary data file

Each
database must have one, and only one, Primary data file. This file type
stores the system tables, data, and also keeps track of the other files that
makeup the database. Primary data files use the extension .MDF

Secondary data files

A
database can have one or more Secondary data files, but none are required.
This file type stores data and uses the extension .NDF

Transaction log files

Each
database must have one or more Transaction log files. This file type is used
to store Transaction information and can be used to recover the database.
Transaction log files use the extension .LDF

A simple
database may have all its data stored in the Primary data file and may only have
one log file. On the other hand, a more complex database may have the Primary
data file, three Secondary data files, and two log files. By using multiple
files a more complex database can spread its load across as many physical disks
as possible. 

Now that
we know about the files that makeup our database, let’s look at how our data is
being stored inside the files. 



When data
is stored, it is placed in an 8 kilobyte contiguous block of disk space known as
a page. Once you remove the overhead needed to manage the page, 8060 bytes are
left for storing data. This brings up an important note and something to
consider, rows can’t span multiple pages. Therefore the maximum size a row can
be is 8060 bytes.

To manage
the pages in a data file, pages are grouped into “extents.” Extents are made up
of a grouping of 8 contiguous data pages (64 kilobytes). Extents can then be
broken into two categories:
mixed extents, which contain
data from up to eight objects in the database, or uniform extents, which contain
data from only one object in the database. When you create a new object it is
allocated space from a mixed extent. Once a table has grown larger than eight
data pages it is from then on allocated uniform extents. By using mixed and
uniform extents, SQL Server can save disk space when an object is very small.

Page 2: Database Filegroups

 »


See All Articles by Columnist
Michael Aubert

Latest Articles