CREATE TABLE Statement

Creates a new table in the database or on a remote server.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
   [ IF NOT EXISTS ] [ owner. ]table-name
   … ( column-definition [ column-constraint ] … 
   [ , column-definition [ column-constraint ] …]
   [ , table-constraint ] … ) 
   |{ ENABLE | DISABLE } RLV STORE
  
   …[ IN dbspace-name ]
   …[ ON COMMIT { DELETE | PRESERVE } ROWS ]
   [ AT location-string ]
   [PARTITION BY 
     range-partitioning-scheme
     | hash-partitioning-scheme 
     | composite-partitioning-scheme ]

column-definition - (back to Syntax)
   column-name data-type 
    [ [ NOT ] NULL ] 
    [ DEFAULT default-value | IDENTITY ] 
    [ PARTITION | SUBPARTITION ( partition-name IN  dbspace-name [ , ... ] ) ]

default-value - (back to column-definition)
   special-value
   | string
   | global variable
   | [ - ] number
   | ( constant-expression )
   | built-in-function( constant-expression )
   | AUTOINCREMENT
   | CURRENT DATABASE
   | CURRENT REMOTE USER
   | NULL
   | TIMESTAMP
   | LAST USER

special-value - (back to default value)
   CURRENT 
   { DATE
   | TIME
   | TIMESTAMP
   | USER
   | PUBLISHER }
   | USER

column-constraint - (back to Syntax)
   [ CONSTRAINT constraint-name ] {
     { UNIQUE  
        | PRIMARY KEY  
        | REFERENCES table-name [ ( column-name ) ] [ action ]  
      }
      [ IN dbspace-name ]
      | CHECK ( condition )
      | IQ UNIQUE ( integer ) 
    }

table-constraint - (back to Syntax)
   [ CONSTRAINT constraint-name ] 
   {  { UNIQUE ( column-name [ , column-name ] … ) 
      | PRIMARY KEY ( column-name [ , column-name ] … ) 
      } 
   [ IN dbspace-name ] 
      | foreign-key-constraint
      | CHECK ( condition ) 
      | IQ UNIQUE ( integer ) 
   }

foreign-key-constraint - (back to table-constraint)
   FOREIGN KEY [ role-name ] [ ( column-name [ , column-name ] … ) ] 
   …REFERENCES table-name [ ( column-name [ , column-name ] … ) ]
   …[ actions ] [ IN dbspace-name ]

actions - (back to foreign-key-constraint)
   [ ON { UPDATE | DELETE } RESTRICT ]

location-string - (back to Syntax) or (back to composite-partitioning-scheme)
   { remote-server-name. [ db-name ].[ owner ].object-name
      | remote-server-name; [ db-name ]; [ owner ];object-name }

range-partitioning-scheme - (back to Syntax)
   RANGE ( partition-key ) ( range-partition-decl [,range-partition-decl ... ] )

partition-key - (back to range-partitioning-scheme) or (back to hash-partitioning-scheme)
   column-name

range-partition-decl - (back to range-partitioning-scheme)
   VALUES <= ( {constant-expr 
      |  MAX } [ , { constant-expr 
      |  MAX }]... ) 
   [ IN dbspace-name ]

hash-partitioning-scheme - (back to Syntax) or (back to composite-partitioning-scheme)
   HASH  ( partition-key [ , partition-key, … ] )

composite-partitioning-scheme - (back to Syntax)
   hash-partitioning-scheme  SUBPARTITION  range-partitioning-scheme

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

You can create a table for another user by specifying an owner name. If GLOBAL TEMPORARY or LOCAL TEMPORARY is not specified, the table is referred to as a base table. Otherwise, the table is a temporary table.

A created global temporary table exists in the database like a base table and remains in the database until it is explicitly removed by a DROP TABLE statement. The rows in a temporary table are visible only to the connection that inserted the rows. Multiple connections from the same or different applications can use the same temporary table at the same time and each connection sees only its own rows. A given connection inherits the schema of a global temporary table as it exists when the connection first refers to the table. The rows of a temporary table are deleted when the connection ends.

When you create a local temporary table, omit the owner specification. If you specify an owner when creating a temporary table, for example, CREATE TABLE dbo.#temp(col1 int), a base table is incorrectly created.

An attempt to create a base table or a global temporary table will fail, if a local temporary table of the same name exists on that connection, as the new table cannot be uniquely identified by owner.table.

You can, however, create a local temporary table with the same name as an existing base table or global temporary table. References to the table name access the local temporary table, as local temporary tables are resolved first.

For example, consider this sequence:

CREATE TABLE t1 (c1 int);
INSERT t1 VALUES (9);

CREATE LOCAL TEMPORARY TABLE t1 (c1 int);
INSERT t1 VALUES (8);

SELECT * FROM t1;

The result returned is 8. Any reference to t1 refers to the local temporary table t1 until the local temporary table is dropped by the connection.

In a procedure, use the CREATE LOCAL TEMPORARY TABLE statement, instead of the DECLARE LOCAL TEMPORARY TABLE statement, when you want to create a table that persists after the procedure completes. Local temporary tables created using the CREATE LOCAL TEMPORARY TABLE statement remain until they are either explicitly dropped, or until the connection closes.

Local temporary tables created in IF statements using CREATE LOCAL TEMPORARY TABLE also persist after the IF statement completes.

SAP Sybase IQ does not support the CREATE TABLE ENCRYPTED clause for table-level encryption of SAP Sybase IQ tables. However, the CREATE TABLE ENCRYPTED clause is supported for SQL Anywhere tables in an SAP Sybase IQ database.

Side Effects
  • Automatic commit

Standards

(back to top)

  • SQL–Vendor extension to ISO/ANSI SQL grammar.

    These are vendor extensions:
    • The { IN | ON } dbspace-name clause
    • The ON COMMIT clause
    • Some of the default values
  • SAP Sybase Database product–Supported by Adaptive Server, with some differences.
    • Temporary tables – you can create a temporary table by preceding the table name in a CREATE TABLE statement with a pound sign (#). These temporary tables are SAP Sybase IQ declared temporary tables, which are available only in the current connection. For information about declared temporary tables, see DECLARE LOCAL TEMPORARY TABLE Statement.
    • Physical placement – physical placement of a table is carried out differently in SAP Sybase IQ and in Adaptive Server. The ON segment-name clause supported by Adaptive Server is supported in SAP Sybase IQ, but segment-name refers to an IQ dbspace.
    • Constraints – SAP Sybase IQ does not support named constraints or named defaults, but does support user-defined data types that allow constraint and default definitions to be encapsulated in the data type definition. It also supports explicit defaults and CHECK conditions in the CREATE TABLE statement.
    • NULL – (default) by default, columns in Adaptive Server default to NOT NULL, whereas in SAP Sybase IQ the default setting is NULL, to allow NULL values. This setting can be controlled using the ALLOW_NULLS_BY_DEFAULT option. See ALLOW_NULLS_BY_DEFAULT Option [TSQL]. To make your data definition statements transferable, explicitly specify NULL or NOT NULL.

Permissions

(back to top)

Table Type Privileges Required
Base table in the IQ main store Table owned by self – Requires CREATE privilege on the dbspace where the table is created. Also requires one of:
  • CREATE TABLE system privilege.
  • CREATE ANY OBJECT system privilege.
Table owned by any user – Requires CREATE privilege on the dbspace where the table is created. Also requires one of:
  • CREATE ANY TABLE system privilege.
  • CREATE ANY OBJECT system privilege.
Global temporary table Table owned by self – Requires one of:
  • CREATE TABLE system privilege.
  • CREATE ANY OBJECT system privilege.
Table owned by any user – Requires one of:
  • CREATE ANY TABLE system privilege.
  • CREATE ANY OBJECT system privilege.
Proxy table Table owned by self – Requires one of:
  • CREATE PROXY TABLE system privilege.
  • CREATE ANY TABLE system privilege.
  • CREATE ANY OBJECT system privilege.
Table owned by any user – Requires one of:
  • CREATE ANY TABLE system privilege.
  • CREATE ANY OBJECT system privilege.
Parent topic: Appendix: SQL Reference