Home

	Expand Search

Transact-SQL User's Guide

Chapter 7 Creating Databases and Tables

Using and creating databases

Creating a user database: create database

Creating a user database: create database

You can create a new database if a System Administrator has granted you permission to use create database. You must be using the master database when you create a new database. In many enterprises, a System Administrator creates all databases. The creator of a database is its owner. Another user who creates a database for you can transfer ownership of it using sp_changedbowner.

The Database Owner is responsible for giving users access to the database and for granting and revoking certain other permissions to users. In some organizations, the Database Owner is also responsible for maintaining regular backups of the database and for reloading it in case of system failure. The Database Owner can temporarily attain any other user's permissions on a database by using the setuser command.

Because each database is allocated a significant amount of space, even if it contains only small amounts of data, you may not have permission to use create database.

The simplest form of create database is:

create database database_name

To create a new database called newpubs database, first verify you are using the master database rather than pubs2, and then type this command:

use master

create database newpubs 

drop database newpubs

use pubs2

A database name must be unique on Adaptive Server, and must follow the rules for identifiers described under "Identifiers". Adaptive Server can manage up to 32,767 databases. You can create only one database at a time. The maximum number of segments for any database is 32.

Adaptive Server creates a new database as a copy of the model database, which contains the system tables that belong in every user database.

The creation of a new database is recorded in the master database tables sysdatabases and sysusages.

The full syntax of create database is:

create database database_name 
     [on {default | database_device} [= size] 
           [, database_device [= size]]...] 
     [log on database_device [= size] 
           [, database_device [= size]]...]
     [with override]
     [for load]

This chapter describes all the create database options except with override. For information about with override, see the System Administration Guide.

The optional on clause allows you to specify where to store the database and how much space in megabytes to allocate for it. If you use the keyword default, the database is assigned to an available database device in the pool of default database devices indicated in the master database table sysdevices. Use sp_helpdevice to see which devices are in the default list.

A System Administrator may have made certain storage allocations based on performance statistics and other considerations. Before creating databases, you should check with a System Administrator.

To specify a size of 5MB for a database to be stored in this default location, use on default = size:

use master

create database newpubs 
on default = 5

drop database newpubs

use pubs2

To specify a different location for the database, give the logical name of the database device where you want it stored. A database can be stored on more than one database device, with different amounts of space on each.

This example creates the newpubs database and allocates 3MB to it on pubsdata and 2MB on newdata:

create database newpubs 
on pubsdata = 3, newdata = 2 

If you omit the on clause and the size, the database is created with 2MB of space from the pool of default database devices indicated in sysdevices.

A database allocation can range in size from 2MB to 223MB.

Unless you are creating very small, noncritical databases, always use the log on database_device extension to create database. This places the transaction logs on a separate database device. There are several reasons for placing the logs on a separate device:

• It allows you to use dump transaction rather than dump database, thus saving time and tapes.

• It allows you to establish a fixed size for the log, keeping it from competing with other database activity for space.

Additional reasons for placing the log on a separate physical device from the data tables are:

• It improves performance.

• It ensures full recovery in the event of hard disk failures.

The following command places the log for newpubs on the logical device pubslog, with a size of 1MB:

create database newpubs 
on pubsdata = 3, newdata = 2 
log on pubslog = 1 

When you use the log on extension, you are placing the database transaction log on a segment named "logsegment". To add more space for an existing log, use alter database and, in some cases, sp_extendsegment. See the Reference Manual or the System Administration Guide for details.

The size of the device required for the transaction log varies according to the amount of update activity and the frequency of transaction log dumps. As a rule of thumb, allocate to the log between 10 and 25 percent of the space you allocate to the database.

The optional for load clause invokes a streamlined version of create database that you can use only for loading a database dump. Use the for load option for recovery from media failure or for moving a database from one machine to another. See the System Administration Guide for more information.