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 {dbid = number, default_location = "pathname", override}]
	[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)

Usage

• You cannot use either with default_location or for proxy_update parameters with the create temporary database command, and 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.

• 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 automatic import of proxy table definitions is not done 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), Component Integration Services 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, 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.