Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 1, 2002

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

By Michael Aubert

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


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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM