continue  create default

Chapter 1: Commands

create database

Description

Creates a new database.

Syntax

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

Parameters

temporary

indicates that you are creating a temporary database.

database_name

is the name of the new database. It must conform to the rules for identifiers and cannot be a variable.

on

indicates a location and size for the database.

default

indicates that create database can put the new database on any default database devices, as shown in sysdevices.status. To specify a size for the database without specifying a location, use:

on default = size

To change a database device’s status to “default,” use sp_diskdefault.

database_device

is the logical name of the device on which to locate the database. A database can occupy different amounts of space on each of several database devices. To add database devices to Adaptive Server, use disk init.

size

is the amount of space to allocate to the database extension. You can use the following unit specifiers, using uppercase, lowecase, single and double quotes interchangeably: ‘k’ or “K” (kilobytes), “m” or ‘M’ (megabytes), “g” or “G” (gigabytes), and ‘t’ or ‘T’ (terabytes). Sybase recommends that you always include a unit specifier. Quotes are optional if you do not include a unit specifier. However, you must use quotes if you include a unit specifier. If you do not provide a unit specifier, the value provided is presumed to be in megabytes.

log on

specifies the logical name of the device for the database logs. You can specify more than one device in the log on clause.

with override

forces Adaptive Server to accept your device specifications, even if they mix data and transaction logs on the same device, thereby endangering up-to-the-minute recoverability for your database. If you attempt to mix log and data on the same device without using this clause, the create database command fails. If you mix log and data, and use with override, you are warned, but the command succeeds.

for load

invokes a streamlined version of create database that you can use only for loading a database dump. See “Using the for load option” for more information.

with default_location

specifies the storage location of new tables. If you also specify the for proxy_update clause, one proxy table for each remote table or view is automatically created from the specified location.

for proxy_update

automatically gets metadata from the remote location and creates proxy tables. You cannot use for proxy_update unless you also specify with default_location.

Examples

Example 1

Creates a database named pubs:

create database pubs

Example 2

Creates a 4MB database named pubs:

create database pubs 
on default = 4

If you do not provide a unit specifier for size, the value provided for pubs is presumed to be in megabytes.

Example 3

Creates a database named pubs with 3MB on the datadev device and 2MB on the moredatadev device:

create database pubs 
    on datadev = "3M", moredatadev = '2.0m'

Example 4

Creates a database named pubs with 3MB of data on the datadev device and a 0.5GB log on the logdev device:

create database pubs
    on datadev='3m'
    log on logdev='0.5g'

Example 5

Creates a proxy database named proxydb but does not automatically create proxy tables:

create database proxydb
with default_location
"UNITEST.pubs.dbo."

Example 6

Creates a proxy database named proxydb and automatically creates proxy tables:

create database proxydb
on default = "4M"
with default_location
"UNITEST.pubs2.dbo."
for proxy_update

Example 7

Creates a proxy database named proxydb, and retrieves the metadata for all of the remote tables from a remote database:

create database proxydb
on default = 4
with default_location
"UNITEST.pubs2.."
for proxy_update

Example 8

Creates a temporary database called mytempdb1, with 3MB of data on the datadev device and 1MB of log on the logdev device:

create temporary database mytempdb1
    on datadev = '3m' log on logdev = '1M'

Example 9

Creates a table with one materialized computed column:

create table mytitles
  (title_id tid not null,
  title varchar(80) not null,
  type char(12) not null,
  pub_id char(4) null,
  price money null,
  advance money null,
  total_sales int null,
  notes varchar(200) null,
  pubdate datetime not null,
    sum_sales compute price * total_sales materialized)

Usage


Restrictions


Temporary databases


New databases created from model


Ensuring database recoverability


Using the for load option

You can use the for load option for recovering from media failure or for moving a database from one machine to another, if you have not added to the database with sp_addsegment. Use alter database for load to create a new database in the image of the database from which the database dump to be loaded was made. For a discussion of duplicating space allocation when loading a dump into a new database, see the System Administration Guide.


Getting information about databases


Using with default_location and for proxy_update

Without the for proxy_update clause, the behavior of the with default_location clause is the same as that provided by sp_defaultloc—a default storage location is established for new and existing table creation, but automatic import of proxy table definitions is not done during the processing of create database.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create database permission defaults to System Administrators, who can transfer it to users listed in the sysusers table of the master database. However, create database permission is often centralized to maintain control over database storage allocation.

If you are creating the sybsecurity database, you must be a System Security Officer.

create database permission is not included in the grant all command.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

9

create

create database

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

See also

Commands alter database, disk init, drop database, dump database, load database, online database

System procedures sp_changedbowner, sp_diskdefault, sp_helpdb, sp_logdevice, sp_renamedb, sp_spaceused




Copyright © 2005. Sybase Inc. All rights reserved. create default

View this book as PDF