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

Friday May 31st 2002 by Michael Aubert

Michael Aubert's fifth article in the SQL Server 2000 Administration in 15 Minutes a Week series covers the files that make up a database and also steps through the process of creating a database.

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

Database Filegroups 

In order to manage data files they need to be placed into "filegroups." By placing files into filegroups, we can control how data is spread over multiple files and we can also direct specific tables to specific files or sets of files.

When SQL Server allocates extents to tables, it does so by the proportional amount of the free space in all files in a filegroup. For example, let's say we had a filegroup that was made up of one file that had 50 MB free and another file that had 100 MB free. For every extent that was allocated from the first file, two extents would be allocated from the second file. Therefore the two data files would fill up at approximately the same time. 

Filegroups also allow control over what files a table will be stored in. For example, say we had 4 hard disks with 1 secondary data file on each disk. In our database let's say we have two large tables that we need to perform frequent join operations on. In order to get the best performance we can setup two file groups each containing two of the four data files. Next we can place each table in one of the filegroups to give us maximum performance. Note that we will see how to place tables and indexes in specific filegroups when we start creating tables in an upcoming article. 

There are a few specific filegroups we need to know about before we start creating our database. First, the Primary data file is always created as part of the "Primary filegroup" and it can't be removed from this group. The Primary filegroup contains all the system objects (system objects are objects that store information about the database) in the database. The system objects stored in the Primary filegroup can't be removed from this group.

When you create a new object it is automatically placed into the "Default" filegroup unless you specify otherwise. The Default filegroup can be set to any group you wish, but by default, the Default filegroup is set to the Primary filegroup (got that?). For example, if you created a new group called "MainStore" and set it as the Default filegroup, any new tables you create would automatically be placed into the "MainStore" filegroup.  

We will come back to the concept of filegroups when we start creating objects. Also, I will show you how to perform tasks such as moving objects between file groups then. So, if you don't have a total grasp on how filegroups fit in...don't worry. In a later article we will also take a more in-depth look at using files and filegroups to optimize database performance.

Page 3: Creating a Database

 » See All Articles by Columnist Michael Aubert

Creating a Database 

Ok, enough with that stuff...let's create our first database! Start by opening Enterprise manager and expand the tree view until you see the "Databases" folder of your server. 

Click to Enlarge

Right Click the Databases folder and select "New Database..." 

In the Name textbox enter "FirstDB"

One thing to note on this screen is the ability to set the collation for this database other than the server default. If you don't remember what the collation is, go back and look over my "Basic Installations" article. For this example we will leave it as the server default. 

Click the "Data Files" tab. 

The top portion of the Data Files tab shows you all the data files that make up this database. As you can see Enterprise Manager has already added the Primary Data file for us. The logical file name is FirstDB_Data and the physical file is named FirstDB_Data.MDF located in the Data folder that was specified when we installed SQL Server. The logical file name is the name that SQL Server uses internally to reference the file, whereas the physical file name is the name of the operating system file. 

In addition to the logical file name and physical name/location, we also can set the Initial size for each file. If you know you will be loading data into your database and have a general idea of its size, you can set this value to avoid automatic file growth (more on this in a second). The last column allows the selection and creation of file groups. 

The lower half of the Data Files tab allows you to set the Automatic file growth for each file that makes up the database. When a database has used all available space in a data file, you can have SQL Server automatically expand the file as needed. The File Growth option allows you to set how much SQL Server will expand the data file. You can select a fixed amount in megabytes or enter a percentage of the current file size to grow the file by. You can also set a maximum file size for the data file. Once a file reaches its maximum size, no more automatic file growth will take place. 

As a general rule, it is best to have files expand as few times as possible because expanding files causes a performance hit to SQL Server. 

Click the "Transaction Log" tab. 

The options on the Transaction Log tab are very similar to the Data Files tab with the only exception being the lacking of a File Group option. Filegroups are only used for data files, not log files. All other options on the Transaction Log tab are the same as the Data Files tab.                                             

Click OK to accept the defaults. 

Our new database now appears in the Databases folder. If you go exploring into the database we have just created you will notice it looks a lot like the model database. This is because all new databases we create are a copy of the model database. All the system objects and other objects we create in the model database are added automatically to all the new databases we create.

Another way to create a database is by using the Create Database Wizard. To start the Create Database Wizard, in Enterprise Manager Select "Wizards" from the "Tools" menu. 

Then expand "Database", select "Create Database Wizard", and click OK. 

The wizard then walks you through the steps to create a database. 

Once again, that will finish it up for this week. Next week we will spend some more time looking at database management.

As always, if you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.


» See All Articles by Columnist Michael Aubert

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved