Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 7 Creating Databases and Tables

Using and creating databases

A database is a collection of related tables and other database objects--views, indexes, and so on.

When you install Adaptive Server, it contains these system databases:

The master database controls the user databases and the operation of Adaptive Server as a whole.
The sybsystemprocs database contains the system stored procedures.
The sybsystemdb database contains information about distributed transactions.
The temporary database, tempdb, stores temporary objects, including temporary tables created with the name prefix "tempdb..".
The model database is used by Adaptive Server as a template for creating new user databases.

In addition, System Administrators can install these optional databases:

pubs2 - a sample database that contains data representing a publishing operation. You can use this database to test your server connections and learn Transact-SQL. Most of the examples in the Adaptive Server documentation query the pubs2 database.
pubs3 - a version of pubs2 that uses referential integrity examples. pubs3 has a table, store_employees, that uses a self-referencing column. pubs3 also includes an IDENTITY column in the sales table. Additionally, the primary keys in its master tables use nonclustered unique indexes, and the titles table has an example of the numeric datatype.
interpubs - similar to pubs2, but contains French and German data.
jpubs - similar to pubs2, but contains Japanese data. Use it if you have installed the Japanese Language Module.

These optional databases are user databases. All of your data is stored in user databases. Adaptive Server manages each database by means of system tables. The data dictionary tables in the master database and in other databases are considered system tables.

Choosing a database: use

The use command lets you access an existing database. Its syntax is:

use database_name

For example, to access the pubs2 database, type:

use pubs2

This command allows you to access the pubs2 database only if you are a known user in pubs2. Otherwise, you see an error message.

It is likely that you will be automatically connected to the master database when you log in to Adaptive Server, so if you want to use another database, issue the use command. You or a System Administrator can change the database to which you initially connect by using sp_modifylogin. Only a System Administrator can change the default database for another user.

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 on clause

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.

The log on clause

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 for load option

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.


	Expand Search