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 list summarizes the three file types.
- 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.