CREATE DATABASE Statement

Creates a new database. You must be in the master database to create a new database.

Syntax

CREATE DATABASE database_name
[ON {DEFAULT | database_device} [= size]
    [, database_device [= size]]...]
[LOG ON database_device [= size]
    [, database_device [= size]]...]
[FOR LOAD]

where

database_name

Is the name of the new database. Database names must conform to the rules for identifiers.

ON

Indicates that you will specify a location and (optionally) a size for the database.

DEFAULT

Indicates that CREATE DATABASE can put the new database on any default database device(s) shown in the sysdevices table. To specify a size for the database without specifying a location, use ON DEFAULT = size. To change the status of a database device to default, use the sp_diskdefault system stored procedure.

database_device

Is the logical name of the database device on which you want to put a database. A database can occupy different amounts of space on each of several database devices. To create a device, use the DISK INIT statement.

size

Specifies the amount of space, in megabytes, allocated to the database. A database can range from 1 to 2^24 MB.

When an explicit value is not supplied, SQL Server will use the larger value between the size of the model database or the configured database size (configured with sp_configure). For example, if database size is configured to 2 (the default) and the model database has been altered to 3MB, the database will be 3MB.

LOG ON

Is the name of the device that stores the database log. You can specify more than one device in the LOG ON clause.

FOR LOAD

Prevents anyone from using the database between the CREATE DATABASE, ALTER DATABASE, and LOAD statements. FOR LOAD can also be specified with the ALTER DATABASE statement, but only if the database was created with FOR LOAD.

When a database is created with FOR LOAD, SQL Server ensures that only the database owner, by setting the database to dbo use only, can use the database. After the database has been loaded, you can change the database option by using the sp_dboption system stored procedure.

Note This capability is similar to that provided in SQL Server 4.21a via trace flag 1802, but FOR LOAD guarantees that only the database owner can use the database between the CREATE and LOAD statements.

Remarks

When the CREATE DATABASE statement is executed, SQL Server makes a copy of the model database (the database catalog). You can update the model database just like any other database and add your own tables, stored procedures, user-defined datatypes, and so on.

In SQL Server 6.0, CREATE DATABASE is intelligent during the initialization of the device(s) on which the database will reside. When executing a CREATE DATABASE statement, SQL Server determines whether the devices are newly created. If the devices have not previously contained data, the devices will not be reinitialized.

SQL Server can manage up to 32,767 databases, each of which can be up to 1 TB in size. Each database can have a maximum of 32 device fragments. Each time you use CREATE DATABASE or ALTER DATABASE to allocate space on a database device, that allocation represents a device fragment and is entered as a row in the sysusages system table.

The total number of physical devices that can be used or the total size of each logical device is not limited (this is limited by the physical device(s) used, with an absolute maximum of 32 GB for each logical device). Instead, only the total number of logical device fragments is limited. Each device fragment can actually exist on one or more physical devices if some form of hardware or software striping is used. Whenever possible, it is recommended that striping is used. Striping can provide performance improvements and, in some cases, fault tolerance. For more information, see the Microsoft SQL Server Administrator's Companion.

If you don't specify a location and size for a database, the default location is any default database device, and the default size is the size of the model database or the configuration setting for database size. To display available devices, use the sp_helpdevice system stored procedure. To see configuration settings, use the sp_configure system stored procedure.

If SQL Server can't give as much space as you've requested, SQL Server creates the database, allocating as much space as possible (in 0.5-MB increments) on each database device, and then prints a message showing how much space was allocated and where.

Each new database inherits its database option settings from the model database. For example, the database option select into/bulkcopy is originally set to "off." You can change that setting for each newly created database by using the sp_dboption system stored procedure in the model database.

It is important to back up the master database with the DUMP DATABASE statement after each use of CREATE DATABASE. This makes recovery easier and safer in case the master database is damaged. (If you create a database and fail to back up master, you may be able to recover the changes with the DISK REFIT statement.)

To display a report on a database or on all the databases on a SQL Server, execute the sp_helpdb system stored procedure. For a report on the space used in a database, use the sp_spaceused system stored procedure.

Using the LOG ON Clause

For full recoverability, place the transaction log on a different physical device or devices from the device that stores the data. If a disk crashes, you can recover the database from database dumps and from the transaction log dumps. Although you can create a database on a single device and store the transaction log together with the rest of the database, this is not recommended because you would have to rely solely on the DUMP DATABASE statement for recovery.

Note If you do not create the database with the LOG ON option, you can still move the transaction log later by using the sp_logdevice system stored procedure.

The size of the database device required for the transaction log varies according to the amount of update activity and the frequency of transaction log dumps. As a rule, allocate to the transaction log 10 to 25 percent of the space you allocate to the database. It is best to start small, since space allocated to a transaction log database device cannot be reclaimed and cannot be used for storage of data.

Permission

CREATE DATABASE permission defaults to the system administrator, who can transfer it. However, CREATE DATABASE permission is often centralized to maintain control over disk allocation. CREATE DATABASE permission is not included when you use the GRANT statement. For more information, see the Microsoft SQL Server Administrator's Companion.

Examples

A.    Create a Single Database on a Single Device

This example creates a 12-MB database called testing on DEVICE1.

CREATE DATABASE testing
ON DEVICE1 = 12

B.    Create a Single Database on Two Devices ¾ Separate Data and Log

This example creates a single database (sales) with the data portion on DEVICE3 and the log portion on DEVICE4.

CREATE DATABASE sales
ON DEVICE3 = 125
LOG ON DEVICE4 = 60

C.    Create a Database FOR LOAD

This example creates a single database (personnel) with the data portion spanning three devices (DEVICE5, DEVICE6, and DEVICE7) and the log portion on DEVICE8. The total database size is 200 MB composed of 150 MB for data and 50 MB for log.

CREATE DATABASE personnel
ON DEVICE5 = 50, DEVICE6 = 50, DEVICE7 = 50
LOG ON DEVICE8 = 50
FOR LOAD
go

See Also