SQL Server Magazine

This month, I cover a topic from the 70-28 SQL Server 7.0 Administration exam: creating and managing databases. September's T-SQL for Starters column ("Creating SQL Server 6.5 Databases") described how to create databases in SQL Server 6.5. SQL Server 7.0 introduced a new approach to storing data, which takes SQL Server to a new level. You no longer have to define devices before building databases. Now you can jump right in and start creating databases. But you have some new options to consider. Let's look at some of the options and how you can use them to build databases. As always inSQL Server, you can achieve your objective through the GUI or through Transact SQL (T-SQL), but I'll use T-SQL statements in this article.

Before You Begin
As with SQL Server 6.5, in SQL Server 7.0 you define the database size in megabytes. Instead of occupying space in a device, a database is one or more files visible on the hard disk. The transaction log is also a separate file or files and needs to be a separate file set from the database. You can no longer put the data and log on the same device, because they would compete for space. A database can grow automatically as you add data, and so can the transaction log. You can set SQL Server to periodically check whether the database can shrink to recover space after data is deleted.

Creating a Database
Only the sa, a member of the sysadmin role, or someone the sa puts into the dbcreator server role can create a database. In SQL Server 7.0, you create a database with a script such as the one in the Create Database listing, which subscribers can download along with all the code and practice exercises for this article at http://www.sqlmag.com. The Create Database listing creates a budget database. The first line of the script specifies that you want to create the database in the Master database. Technically, shifting the focus to the Master database isn't necessary because the Master database is the only place where you can create a database definition. However, specifying the location reminds you to back up the Master database after you create a database. The database name follows the CREATE DATABASE statement. Then you must specify the logical file name—the name by which SQL Server refers to the database file—and a physical filename, including the path and the directory, for the data file. By convention, use the extension .mdf for the Primary file (which I'll cover later in this article). The example in the create database file puts the budgetdata.mdf file in the C:\mssql7\data directory. SQL Server builds a data directory below the main installation directory. SQL Server uses the data directory as the default data file directory, although you can put your data files anywhere on a local disk (not a network disk). The file starts at 100MB.

Automatic file growth sounds useful, especially if you've ever run out of space on a SQL Server 6.5 database and had to expand the devices and databases. However, you always face the danger of a runaway INSERT statement causing the database to take over the entire disk. (Fortunately, a database file can't expand onto another disk.) If you want to set an upper limit on database growth, use the MAXSIZE option. If you don't set a maximum size, by default the database grows until it fills the disk. To prevent the database from growing automatically, make sure that you set the FILEGROWTH parameter to zero. You can also choose whether the database grows by a fixed number of megabytes or by a percentage of the database size at the time growth occurs. In either case, avoid growing it in tiny increments, which is time-consuming; grab a reasonably sized increment each time. The default, unless you change it, is 10 percent. The database always grows by at least 64KB, in multiples of 64KB, because it allocates space in extents, which are eight 8KB pages. (See Inside SQL Server, "The New Space Management," April 1999, for more information about extents.) If the data needs to grow beyond the limits of one disk, you can spread the data across multiple files on multiple disks—or just use a RAID array.

The transaction log goes in a separate file, with the extension .ldf. The example creates the log in the default C:\mssql7\data directory, but usually it's on another disk. As with the data file, you need to provide both a logical and a physical file name, and the same options for file growth and increment that apply to the data file also apply to the log file. You can spread the transaction log across multiple files or filegroups if it becomes too large for one physical disk. The database creation syntax has a few inconsistencies. For example, the name of the database is a character value, but you must not enclose it in quotes. The logical name of the file is also a character field; you can enclose it in quotes, but you don't need to. And the name of the physical file must be in quotes. However, you can use square brackets instead of quotes if you prefer.

Modifying the Database
Even with automatic file growth, you might need to expand the database to another disk. You use an ALTER DATABASE command for expansion:

ALTER DATABASE Budget
ADD FILE 
    ( NAME = Budget_data2,
     FILENAME = 'd:\sqldata\budgetdata2.ndf'  ,
     SIZE = 200, MAXSIZE = 500, FILEGROWTH = 50 )

By convention, use the .ndf extension for secondary files.

Files and Filegroups
To spread the database across more than one disk, you need both primary and secondary files. A database can have only one primary file. SQL Server 7.0 also introduced the concept of filegroups. A filegroup is an administrative grouping of database files you use for allocating tables and indexes and placing them on specific disks. A filegroup can contain one or more files. Although it's optional in this example, I might have included the keyword PRIMARY when I created the database, thus:

CREATE DATABASE Budget
ON PRIMARY( NAME = Budget_data, ...

   Prev. page   [1] 2     next page