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} ]
		[ [,] 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

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

Example 20

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

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

Example 21

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 22

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

• You cannot use either with default_location or for proxy_update parameters with the create temporary database command. Doing so generates an error, such as the following two examples:

  1> create temporary database tb1 with default_location 
       "remSERVER.mydb.." 
  

  Msg 102, Level 15, State 7:
  Server 'ebi_SUS_AS125x_SUN32', Line 1:
  Incorrect syntax near 'create temporary database'.
  

  1> create temporary database tb1 with default_location
       "remSERVER.mydb.." for proxy_update
  

  Msg 102, Level 15, State 7:
  Server 'ebi_SUS_AS125x_SUN32', Line 1:
  Incorrect syntax near 'create temporary database'.
  

• The temporary status of a database, which is set during the creation of the temporary database, is indicated by value 0x00000100 (256 decimal) of the status3 field of a sysdatabases entry.

• In addition to all options inherited from model, a temporary database, like the system tempdb, has the following database options set:

• • select into/bulkcopy

  • trunc log on chkpt

• As with system tempdb, the guest user is added to the temporary database, and create table permission is granted to PUBLIC.

• Unused pages are not cleared during creation of the temporary database, since a temporary database is re-created every time the server is restarted.

• The compression setting for create table . . .with compression overrides the create database compression setting.

• Temporary tables created with select into do not inherit the compression level from the database.

• The default setting for compression in the model database is none (data compression is off for all databases based on model).

• When you enable data compression in tempdb or other temporary databases, temporary tables created in a session or inside stored procedures do not inherit tempdb’s compression level.

• The database to which a task is bound determines the compression level of the temporary tables it creates.

• The in-row size can be as large as the maximum row size allowed in the database. Adaptive Server lowers the limit on the size of individual column’s in-row LOB storage during inserts and updates, based on the space available for storage in a single page, minus any page or row overheads.

• When you create a table in a database where you have specified a valid in-row LOB length database-wide, all LOB columns in the table are created as in-row unless you specify off row in the syntax for the column’s definition. The column’s in-row length, in bytes, is specified by this database-wide setting.

• The default value for inrow_lob_length setting is 0 bytes, which causes no changes in behavior to existing databases when you upgrade to Adaptive Server version 15.7. Changing this default allows applications with different requirements on in-row storage to control how much LOB data is stored in-row.

• The database-wide setting applies only to newly created tables or LOB columns added to existing tables after the database-wide setting is applied or changed. The in-row LOB length inherited by each LOB column when the table was initially created remains unchanged, even when the database-wide setting is altered. To change an individual LOB column’s in-row length or in-row property, use alter table modify column.

• To change the default length, use the inrow_lob_length parameter of alter database.

• The database_device you list for the in-memory database must be an in-memory storage device.

• The for load parameter indicates that the database is created initially in a state waiting to be loaded using a load database command.

• You cannot:

  • Create an in-memory database on a default device.

  • Use in-memory databases for system databases (other than tempdb).

  • Use an in-menory database as an archive database.Sybase reommends that you do not use an in-memory database as a scratch database.

  • Use the same name for the database you are creating and its template database.

  • Specify system databases, including model, as the template database.

  • Mix disk-based and cache-based storage devices. Adaptive Server treats databases created entirely on in-memory storage cache as in-memory databases. You cannot use:

    • In-memory storage devices created on different in-memory storage caches for one in-memory database

    • In-memory storage devices created on one in-memory storage cache, either in full or in part, for different in-memory databases

  • Use the use as template parameter with the with default_location = parameter.

  • Use the for load and for proxy update parameters with the use as template parameter.

• You can:

  • Create in-memory databases on in-memory storage devices residing on named caches as long as all the in-memory storage devices are hosted by in-memory storage cache.

  • Create mixed log-and-data in-memory databases on the same in-memory storage devices. However, mixed log-and-data in-memory databases must be on a single cache.

• The durability = no_recovery parameter is required when you create an an in-memory database. This parameter reinforces the behaviour that in-memory databases are always recreated when you restart the server.

• Adaptive Server creates a new database by copying the model database.

• You can customize model by adding tables, stored procedures, user-defined datatypes, and other objects, and by changing database option settings. New databases inherit these objects and settings from model.

• To guarantee recoverability, create database must clear every page that was not initialized when the model database was copied. This may take several minutes, depending on the size of the database and the speed of your system.

  If you are creating a database to load a database dump into it, you can use the for load option to skip the page-clearing step. This makes database creation considerably faster.

• To get a report on a database, execute sp_helpdb.

• For a report on the space used in a database, use sp_spaceused.

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.

• If for proxy_update is specified with no default_location, an error is reported.

• When a proxy database is created (using the for proxy_update option), CIS is called upon to:

  • Provide an estimate of the database size required to contain all proxy tables representing the actual tables and views found in the primary server’s database. This estimate is the number of database pages needed to contain all proxy tables and indexes. The estimate is used if no size is specified, and no database devices are specified.

  • Create all proxy tables representing the actual tables and views found in the companion server’s database.

  • Grant all permissions on proxy tables to public.

  • Add the guest user to the proxy database.

  • The database status is set to indicate that this database “Is_A_Proxy”. This status is contained in master.dbo.sysdatabases.status3.

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, to maintain control over database storage allocation, create database permission is often centralized.

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.