create database

Description

Creates a new database.

Syntax

Syntax for nonclustered 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} ]
		[, [no] async_init]
		[ [,] compression = {none | row | page}]
		[ [,] lob_compression = {compression_level | off}]
		[ [,] inrow_lob_length = value ] 		}...
	[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, which 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, lowercase, 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.

[no] async_init

enables or disables asynchronous database initialization.

compression

indicates the level of compression to be applied to newly created tables or partitions:

  • none – data is not compressed.

  • row – compresses one or more data items in an individual row. Adaptive Server stores data in a row-compressed form only if the compressed form saves space compared to an uncompressed form.

  • page – when the page fills, existing data rows that are row-compressed are then compressed using page-level compression to create page-level dictionary, index, and character-encoding entries.

    Adaptive Server compresses data at the page level only after it has compressed data at the row level, so setting the compression to page implies both page and row compression.

lob_compression = off | compression_level

Determines the compression level for the newly created table. Selecting off means the table does not use LOB compression.

The compression algorithm ignores rows that do not use LOB data.

Table compression level. The compression levels are:

  • 0 – the row is not compressed.

  • 1 through 9 – Adaptive Server uses ZLib compression. Generally, the higher the compression number, the more Adaptive Server compresses the LOB data, and the greater the ratio between compressed and uncompressed data (that is the greater the amount of space savings, in bytes, for the compressed data versus the size of the uncompressed data).

    However, the amount of compression depends on the LOB content, and the higher the compression level , the more CPU-intensive the process. That is, level 9 provides the highest compression ratio but also the heaviest CPU usage.

  • 100 – Adaptive Server uses FastLZ compression. The compression ratio that uses the least CPU usage; generally used for shorter data.

  • 101 – Adaptive Server uses FastLZ compression. A value of 101 uses slightly more CPU than a value of 100, but uses a better compression ratio than a value of 100.

inrow_lob_length = value

specifies the number of bytes. The range of valid values for inrow_lob_length is 0 through the logical page size of the database. A value of 0 turns off LOB specification database-wide, and all LOB columns without a specific in row clause are created as off-row LOB columns.

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

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 11

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 12

In a cluster environment, creates a global temporary database:

create global temporary database global_tempdb1

Example 13

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 14

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 15

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 16

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 17

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 18

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

Example 19

Creates the emaildb database, and configures it for page-level compression:

create database emaildb
on email_dev = '50M'
with compression = page

Example 20

Creates the email_lob_db database and configures it for a LOB compression level of 101:

Create database email_lob_db
on email_lob_dev = '50M'
with lob_compression = 101

Example 21

Creates a database called pubs that allows in-row LOB data with a length of 300 bytes:

create database pubs
    with inrow_lob_length = 300

Usage


Restrictions


Temporary databases


Creating compressed databases


Creating databases with in-row LOBs


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 proxy table definitions are not automatically imported during the processing of create database.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for create database differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must have create database privilege. If you are creating the sybsecurity database, you must have the manage auditing privilege.

Granular permissions disabled

With granular permissions disabled, you must be system administrator or have create database privilege. If you are creating the sybsecurity database, you must be a system security officer.

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 is 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