create database

Description

Creates a new database.

Syntax

Syntax for non-clustered environments:

create [inmemory] [temporary] database database_name
	[use database_name as template]
	[on {default | database_device} [= size] 
		[, database_device [= size]]...] 
	[log on database_device [= size] 
		[, database_device [= size]]...]
	[with {dbid = number, default_location = "pathname", override}]
		| [[,]durability = { no_recovery
			| at_shutdown
			| full} ]
		}...
	[for {load | proxy_update}]

Syntax for cluster environments:

create [ [ global | system ] temporary ] database database_name 
	[ for instance instance_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.

inmemory

required for in-memory databases.

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

can be specified in any order. You must specify at least one of the following options when you use the with clause:

  • with dbid = number – specifies the dbid for the new database. If you do not explicitly specify the dbid, the server assigns an unused dbid.

  • 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.

  • 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.

durability =

determines the durability level of the database:

  • full – all transactions are written to disk. This is the default if you do not specify a durability level when you create the database, and ensures full recovery from a server failure. All system databases use this durability level (the traditional durability level for disk-resident databases).

  • no_recovery – transactions are not durable to disk and all changes are lost if the server fails or is shut down. For disk-based databases, Adaptive Server periodically writes data at runtime to the disk devices, but in an uncontrolled manner. After any shutdown (polite, impolite, or server failure and restart) a database created with no_recovery is not recovered, but is re-created from the model or template (if defined) database.

  • at_shutdown – transactions are durable while the server is running and after a polite shutdown. All durability is lost if the server fails.

dml_logging

specifies the logging level for DML operations.

full | minimal

specifies either full logging or minimal logging of the DML operations

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.

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.

global temporary

indicates that you are creating a global temporary database.

system temporary

indicates that you are creating a local system temporary database.

temporary

indicates that you are creating a temporary database.

for instance instance_name

specifies the instance that is to own the local system temporary database or local temporary database you are creating. This parameter is not used when creating global temporary databases.

NoteYou must create a local user temporary database from the instance that is to own it. You can create a local system temporary database from any instance.

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 database called pubs with dbid 15:

create database pubs with dbid = 15

Example 9

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 10

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)

Example 11

In a cluster environment, creates a local user temporary database on “ase1.” Execute the following command from the owner instance (“ase1”).

create temporary database local_tempdb1 for instance
ase1

or,

create temporary database local_tempdb1

Example 12

In a cluster environment, creates a local system temporary database on “ase1.” Execute this command from any instance in the cluster.

create system temporary database local_systempdb1 for
instance ase1

Example 13

In a cluster environment, creates a global temporary database.

create global temporary database global_tempdb1

Example 14

Creates an in-memory database on two different in-memory storage devices, imdb_data_dev1 for the data and imdb_logdev for the log:

create inmemory database imdb2
on imdb_data_dev1 = '1.0g'
log on imdb_logdev = '0.5g'
with durability = no_recovery

Example 15

Creates an in-memory database on multiple in-memory storage devices. imdb_data_dev1 and imdb_data_dev2 contain all data, and inmem_logdev contains the log:

create inmemory database imdb3
on imdb_data_dev1 = '100m',
   imdb_data_dev2 = '200m'
log on inmem_logdev = '50m'
with durability=no_recovery

Example 16

Creates the pubs5 database using the pubs2 database as the template:

create inmemory database pubs5
use pubs2 as template
on imdb_duck1_cach = '5m'
log on imdb_duck_log = '5m'
with durability = no_recovery

Example 17

Creates a relaxed-durability database named pubs5_rddb:

create database pubs5_rddb on pubs5_dev = '6M'
log on pubs5_log = '2M'
with durability = at_shutdown

Example 18

Creates an in-memory storage cache dedicated to an in-memory temporary database:

  1. Create the in-memory storage cache:

    sp_cacheconfig inmem_tempdb_cache, "40m", inmemory_storage, 
    "none", "cache_partition=2"
    
  2. Create an in-memory device to create temporary database:

    DISK INIT name = "inmem_dev"
    , physname = "inmem_tempdb_cache"
    , size = "40m"
    , type='inmemory'
    
  3. Create the in-memory database:

    create inmemory temporary database temp_imdb
    on inmem_dev = "20m"
    with durability = no_recovery
    

Example 19

Creates a temporary database on an existing disk-device with durability set to no_recovery:

create temporary database tempdb_rddb_norec
on datadev = "5m" log on logdev = "5m"
with durability = no_recovery

Usage


Restrictions


Temporary databases


Creating in-memory and relaxed durability 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.

Any user with create database privilege can also create a new database with logging disabled. Only the database owner or a login with sa_role privilege can run alter database to change the database-wide logging setting. The user creating a database from a template must be the owner of the template database or a user with the sa_role.

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