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

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. 

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.