Using the CREATE DATABASE Statement to Create an MS-SQL Server Database and Transaction Log

Unlike many other DBMS products, MS-SQL Server lets you create multiple databases for each MS-SQL Server. Most commercial DBMS products do not even have a CREATE DATABASE command. Instead, the installation program creates the one database file the SQL Server will use. The database administrator (dba) and privileged users then create all of the database objects in the one database. As a result, the typical database contains a mix of both related and unrelated tables.

MS-SQL Server gives you the best of both worlds. If you want, you can create a single database for all of your tables, or you can separate totally unrelated tables into separate databases. Suppose, for example, that you and your spouse each run your own home business. Using the typical DBMS, you would create one database to hold both your (mail order) CUSTOMER list and your spouse's (accounting) CLIENT list, even though the two tables are completely unrelated.

Having a single database means that both businesses would lose database access during backup and (if necessary) recovery operations. If the two were separate, you could still use a single server (to save a bit of hard-earned cash on software and hardware), but you would not be affected by database problems or maintenance activities that have nothing to do with your own tables.

Finally, MS-SQL Server's multiple database strategy makes it possible to create a development database that uses the same database objects and security setup as its production counterpart. Having an identical database structure and security setup makes it easier to test how proposed changes will affect online application programs, database stored procedures, views, and triggers. Moreover, once you've fully tested new or modified code on the development system, you will be able to install procedures, triggers, and views on the production system without further modification. Finally, you can import data from tables in the production database into identical tables in the development database, making it easy to use the development system to "freeze" database data and reproduce errors that seem to occur at random intervals.

The syntax of the CREATE DATABASE statement is:

 CREATE DATABASE 
[ON {[PRIMARY] } [,...]]
[LOG ON { } [,...]]
[FOR RESTORE]
 is defined as:
  (NAME = ,
   FILENAME = ''
   [, SIZE = ]
   [, MAXSIZE = { | UNLIMITED}]
   [, FILEGROWTH = ])

Review Table 41.1 for a brief explanation of CREATE DATABASE keywords and options.

Table 41.1: Definition of CREATE DATABASE Statement Keywords and Options

Keyword/Option	Description
database name	The name of the database.
ON	The name(s) of the disk file(s) that will hold the data portion of the database. MS-SQL Server lets you split a single database into multiple files.
PRIMARY	If you split the database into multiple files, PRIMARY identifies the file that contains the start of the data and the system tables. If you don't specify a PRIMARY file, MS-SQL Server will use the first file in the list as the PRIMARY file.
LOG ON	The name(s) of the disk file(s) that will hold the transaction log.
FOR RESTORE	Do not allow access to the database until it is filled with data by a RESTORE operation.
	The name the MS-SQL Server will use to reference the database or transaction log.
	The full pathname to the database or transaction log file.
	The initial size, in megabytes, of the database or transaction log. If you don't specify an initial size for the transaction log, the system will size it to 25 percent of the total size of the database files.
	The maximum size to which the database or transaction log can grow. If you specify UNLIMITED, the files can grow until they exhaust the physical disk space.
	The number of bytes to add to the size of the transaction log or database file when the current free space in the file is used up.

To create a database using the MS-SQL Server Query Analyzer, perform the following steps:

1. Click your mouse on the Start button. Windows will display the Start menu.

2. Move your mouse pointer to Programs on the Start menu, select the Microsoft SQL Server 7.0 option, and click your mouse on Query Analyzer. Query Analyzer will display the Connect to SQL Server dialog box similar to that shown in Figure 41.1.

The Query Analyzer Connect to SQL Server dialog box

1. Enter the name of your SQL Server in the SQL Server field.

2. Enter your username in the Login Name field, and enter your password in the Password field.

3. Click on the OK button. Query Analyzer will connect to the SQL Server you entered in Step 3 and display the Query pane in the SQL Server Query Analyzer application window.

4. Enter the CREATE DATABASE statement. For the current example, enter:

   CREATE DATABASE SQLTips
   ON     (NAME       = SQLTips_data,
          FILENAME   = 'c:\mssql7\data\SQLTips_data.mdf',
          SIZE       = 10,
          FILEGROWTH = 1MB)
   LOG ON (NAME = 'SQLTips_log',
          FILENAME = 'c:\mssql7\data\SQLTips_log.ldf',
          SIZE = 3,
          FILEGROWTH = 1MB)
   

5. Click on the green arrow Execute Query button on the standard toolbar (or select the Query menu Execute option). Query Analyzer will create the database on the SQL Server to which you connected in Step 5.

After you complete Step 7, the Query Analyzer will display the results of the CREATE DATABASE execution in the Results pane in the SQL Server Query Analyzer application window. If Query Analyzer is successful in executing your CREATE DATABASE statement, the program will display the following in the Results pane:

 The CREATE DATABASE process is allocating 10.00 MB on disk   'SQLTips_data'.
The CREATE DATABASE process  is allocating 3.00 MB on disk  "SQLTips_log'.