create database

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}]
    [ encrypt with key_name]

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 an SAP ASE 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). You should 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 the SAP ASE 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, the SAP ASE 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. The SAP ASE 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.
  
  The SAP ASE 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 – the SAP ASE server uses ZLib compression. Generally, the higher the compression number, the more the SAP ASE 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 – the SAP ASE server uses FastLZ compression. The compression ratio that uses the least CPU usage; generally used for shorter data.
- 101 – the SAP ASE 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.
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.
Note: You 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.
encrypt with key_name – creates a fully encrypted database. key_name is the name of the database encryption key.

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:

Create the in-memory storage cache:

sp_cacheconfig inmem_tempdb_cache, "40m", inmemory_storage, 
"none", "cache_partition=2"

Create an in-memory device to create temporary database:

DISK INIT name = "inmem_dev"
, physname = "inmem_tempdb_cache"
, size = "40m"
, type='inmemory'

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
```
Example 22 – Creates an encrypted database called demodb with a log called demodev on a machine called demologdev, using an encrypttion key called dbkey:
```
create database demodb on demodev log on demologdev encrypt with dbkey
```

Usage

Use create database from the master database.
You can specify the size as a float datatype, however, the size is rounded down to the nearest multiple of the allocation unit.
If you do not explicitly state the size of the database, the size is determined by the size of the model database. The minimum size that you can create a database is four allocation units.
Because the SAP ASE server allocates space for databases for create database and alter database in chunks of 256 logical pages, these commands round the specified size down to the nearest multiple of allocation units.
If you do not include a unit specifier, the SAP ASE server interprets the size in terms of megabytes of disk space, and this number is converted to the logical page size the server uses.
If you do not specify a location and size for a database, the default location is any default database devices indicated in master..sysdevices. The default size is the larger of the size of the model database or the default database size parameter in sysconfigures.

system administrators can increase the default size by using sp_configure to change the value of default database size and restarting the SAP ASE server. The default database size parameter must be at least as large as the model database. If you increase the size of the model database, you must also increase the default size.

If the SAP ASE server cannot give you as much space as you want where you have requested it, it comes as close as possible, on a per-device basis, and prints a message telling how much space was allocated and where it was allocated. The maximum size of a database is system-dependent.
If you create a proxy database using:
```
create database mydb on my_device 
with default_location = "pathname" for proxy_update
```
The presence of the device name is enough to bypass size calculation, and this command may fail if the default database size (the size of the model database) is not large enough to contain all of the proxy tables.
To allow CIS to estimate database size, do not include any device name or other option with this command:
```
create database mydb
with default_location = "pathname" for proxy_update
```
Specify whether to encrypt a database when you create it, and all the data inserted into the database becomes encrypted automatically. The size of the database does not change when it is encrypted, and all storage access functions work identically whether a database is encrypted or not. The types of databases that support encryption are:
- Normal user database
- Temporary database
- Archive database
You cannot encrypt an in-memory database.

See also sp_changedbowner, sp_diskdefault, sp_helpdb, sp_logdevice, sp_renamedb, sp_spaceused in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Setting	Description
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.
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:

Information	Values
Event	9
Audit option	create
Command or access audited	create database
Information in `extrainfo`	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