CREATE TABLE Statement

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)

• IN – used in the column-definition, column-constraint, table-constraint, foreign-key, and partition-decl clauses to specify the dbspace where the object is to be created. If the IN clause is omitted, SAP Sybase IQ creates the object in the dbspace where the table is assigned.

  Specify SYSTEM with this clause to put either a permanent or temporary table in the catalog store. Specify IQ_SYSTEM_TEMP to store temporary user objects (tables, partitions, or table indexes) in IQ_SYSTEM_TEMP or, if the TEMP_DATA_IN_SHARED_TEMP option is set 'ON', and the IQ_SHARED_TEMP dbspace contains RW files, in IQ_SHARED_TEMP. (You cannot specify the IN clause with IQ_SHARED_TEMP.) All other use of the IN clause is ignored. By default, all permanent tables are placed in the main IQ store, and all temporary tables are placed in the temporary IQ store. Global temporary and local temporary tables can never be in the IQ store.

  The following syntax is unsupported:

  CREATE LOCAL TEMPORARY TABLE tab1(c1 int) IN IQ_SHARED_TEMP

  A BIT data type column cannot be explicitly placed in a dbspace. The following is not supported for BIT data types:

  CREATE TABLE t1(c1_bit bit IN iq_main); 

• ON COMMIT – allowed for temporary tables only. By default, the rows of a temporary table are deleted on COMMIT.

• AT – creates a proxy table that maps to a remote location specified by the location-string clause. Proxy table names must be 30 characters or less. The AT clause supports semicolon (;) delimiters. If a semicolon is present anywhere in the location-string clause, the semicolon is the field delimiter. If no semicolon is present, a period is the field delimiter. This allows file names and extensions to be used in the database and owner fields.

  Semicolon field delimiters are used primarily with server classes not currently supported; however, you can also use them in situations where a period would also work as a field delimiter. For example, this statement maps the table proxy_a to the SQL Anywhere database mydb on the remote server myasa:

  CREATE TABLE proxy_a1
  AT 'myasa;mydb;;a1'

  Foreign-key definitions are ignored on remote tables. Foreign-key definitions on local tables that refer to remote tables are also ignored. Primary key definitions are sent to the remote server if the server supports primary keys.

  In a simplex environment, you cannot create a proxy table that refers to a remote table on the same node. In a multiplex environment, you cannot create a proxy table that refers to the remote table defined within the multiplex.

• IF NOT EXISTS – if the named object already exists, no changes are made and an error is not returned.

• { ENABLE | DISABLE } RLV STORE – registers this table with the RLV store for real-time in-memory updates. Not supported for IQ temporary tables. This value overrides the value of the database option BASE_TABLES_IN_RLV. Requires the CREATE TABLE system privilege and CREATE permissions on the RLV store dbspace to set this value to ENABLE.

• column-definition – defines a table column. Allowable data types are described in Reference: Building Blocks, Tables, and Procedures >SQL Data Types. Two columns in the same table cannot have the same name. You can create up to 45,000 columns; however, there might be performance penalties in tables with more than 10,000 columns.
  • [ NOT ] NULL ] – includes or excludes NULL values. If NOT NULL is specified, or if the column is in a UNIQUE or PRIMARY KEY constraint, the column cannot contain any NULL values. The limit on the number of columns per table that allow NULLs is approximately 8*(database-page-size - 30).
  • DEFAULT default-value – specify a default column value with the DEFAULT keyword in the CREATE TABLE (and ALTER TABLE) statement. A DEFAULT value is used as the value of the column in any INSERT (or LOAD) statement that does not specify a column value.
  • DEFAULT AUTOINCREMENT – the value of the DEFAULT AUTOINCREMENT column uniquely identifies every row in a table. Columns of this type are also known as IDENTITY columns, for compatibility with Adaptive Server. The IDENTITY/DEFAULT AUTOINCREMENT column stores sequential numbers that are automatically generated during inserts and updates. When using IDENTITY or DEFAULT AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type, with scale 0. The column value might also be NULL. You must qualify the specified table name with the owner name.

    ON inserts into the table. If a value is not specified for the IDENTITY/DEFAULT AUTOINCREMENT column, a unique value larger than any other value in the column is generated. If an INSERT specifies a value for the column, it is used; if the specified value is not larger than the current maximum value for the column, that value is used as a starting point for subsequent inserts.

    Deleting rows does not decrement the IDENTITY/AUTOINCREMENT counter. Gaps created by deleting rows can only be filled by explicit assignment when using an insert. The database option IDENTITY_INSERT must be set to the table name to perform an insert into an IDENTITY/AUTOINCREMENT column.

    For example, this creates a table with an IDENTITY column and explicitly adds some data to it:

    CREATE TABLE mytable(c1 INT IDENTITY);
    SET TEMPORARY OPTION IDENTITY_INSERT = "DBA".mytable;
    INSERT INTO mytable VALUES(5);

    After an explicit insert of a row number less than the maximum, subsequent rows without explicit assignment are still automatically incremented with a value of one greater than the previous maximum.

    You can find the most recently inserted value of the column by inspecting the @@identity global variable.

  • IDENTITY – a Transact-SQL® -compatible alternative to using the AUTOINCREMENT default. In SAP Sybase IQ, the identity column may be created using either the IDENTITY or the DEFAULT AUTOINCREMENT clause.

• table-constraint – helps ensure the integrity of data in the database. There are four types of integrity constraints:
  • UNIQUE – identifies one or more columns that uniquely identify each row in the table. No two rows in the table can have the same values in all the named columns. A table may have more than one unique constraint.
  • PRIMARY KEY – the same as a UNIQUE constraint except that a table can have only one primary-key constraint. You cannot specify the PRIMARY KEY and UNIQUE constraints for the same column. The primary key usually identifies the best identifier for a row. For example, the customer number might be the primary key for the customer table.
  • FOREIGN KEY – restricts the values for a set of columns to match the values in a primary key or uniqueness constraint of another table. For example, a foreign-key constraint could be used to ensure that a customer number in an invoice table corresponds to a customer number in the customer table.

    You cannot create foreign-key constraints on local temporary tables. Global temporary tables must be created with ON COMMIT PRESERVE ROWS.

  • CHECK – allows arbitrary conditions to be verified. For example, a check constraint could be used to ensure that a column called Gender contains only the values male or female. No row in a table is allowed to violate a constraint. If an INSERT or UPDATE statement would cause a row to violate a constraint, the operation is not permitted and the effects of the statement are undone.

    Column identifiers in column check constraints that start with the symbol ‘@’ are placeholders for the actual column name. A statement of the form:

    CREATE TABLE t1(c1 INTEGER CHECK (@foo < 5))

    is exactly the same as this statement:

    CREATE TABLE t1(c1 INTEGER CHECK (c1 < 5))

    Column identifiers appearing in table check constraints that start with the symbol ‘@’are not placeholders.

  If a statement would cause changes to the database that violate an integrity constraint, the statement is effectively not executed and an error is reported. (Effectively means that any changes made by the statement before the error was detected are undone.)

  SAP Sybase IQ enforces single-column UNIQUE constraints by creating an HG index for that column.

  Note: You cannot define a column with a BIT data type as a UNIQUE or PRIMARY KEY constraint. Also, the default for columns of BIT data type is to not allow NULL values; you can change this by explicitly defining the column as allowing NULL values.

• column-constraint – restricts the values the column can hold. Column and table constraints help ensure the integrity of data in the database. If a statement would cause a violation of a constraint, execution of the statement does not complete, any changes made by the statement before error detection are undone, and an error is reported. Column constraints are abbreviations for the corresponding table constraints. For example, these are equivalent:

  CREATE TABLE Products (
  	product_num integer UNIQUE
  )
  CREATE TABLE Products (
  	product_num integer,
  	UNIQUE ( product_num )
  )

  Column constraints are normally used unless the constraint references more than one column in the table. In these cases, a table constraint must be used.

  • IQ UNIQUE – defines the expected cardinality of a column and determines whether the column loads as Flat FP or NBit FP. An IQ UNIQUE(n) value explicitly set to 0 loads the column as Flat FP. Columns without an IQ UNIQUE constraint implicitly load as NBit up to the limits defined by the FP_NBIT_AUTOSIZE_LIMIT, FP_NBIT_LOOKUP_MB, and FP_NBIT_ROLLOVER_MAX_MB options:
    • FP_NBIT_AUTOSIZE_LIMIT limits the number of distinct values that load as NBit
    • FP_NBIT_LOOKUP_MB sets a threshold for the total NBit dictionary size
    • FP_NBIT_ROLLOVER_MAX_MB sets the dictionary size for implicit NBit rollovers from NBit to Flat FP
    • FP_NBIT_ENFORCE_LIMITS enforces NBit dictionary sizing limits. This option is OFF by default
    Using IQ UNIQUE with an n value less than the FP_NBIT_AUTOSIZE_LIMIT is not necessary. Auto-size functionality automatically sizes all low or medium cardinality columns as NBit. Use IQ UNIQUE in cases where you want to load the column as Flat FP or when you want to load a column as NBit when the number of distinct values exceeds the FP_NBIT_AUTOSIZE_LIMIT.
  Note:

  • Consider memory usage when specifying high IQ UNIQUE values. If machine resources are limited, avoid loads with FP_NBIT_ENFORCE_LIMITS='OFF' (default).

    Prior to SAP Sybase IQ 16.0, an IQ UNIQUE n value > 16777216 would rollover to Flat FP. In 16.0, larger IQ UNIQUE values are supported for tokenization, but may require significant memory resource requirements depending on cardinality and column width.

  • BIT, BLOB,and CLOB data types do not support NBit dictionary compression. If FP_NBIT_IQ15_COMPATIBILITY=’OFF’, a non-zero IQ UNIQUE column specification in a CREATE TABLE or ALTER TABLE statement that includes these data types returns an error.

• column-constraint and table-constraint clauses – column and table constraints help ensure the integrity of data in the database.
  • PRIMARY KEY or PRIMARY KEY ( column-name, … ) – the primary key for the table consists of the listed columns, and none of the named columns can contain any NULL values. SAP Sybase IQ ensures that each row in the table has a unique primary key value. A table can have only one PRIMARY KEY.

    When the second form is used (PRIMARY KEY followed by a list of columns), the primary key is created including the columns in the order in which they are defined, not the order in which they are listed.

    When a column is designated as PRIMARY KEY, FOREIGN KEY, or UNIQUE, SAP Sybase IQ creates a High_Group index for it automatically. For multicolumn primary keys, this index is on the primary key, not the individual columns. For best performance, you should also index each column with a HG or LF index separately.

  • REFERENCES primary-table-name [(primary-column-name)] – defines the column as a foreign key for a primary key or a unique constraint of a primary table. Normally, a foreign key would be for a primary key rather than an unique constraint. If a primary column name is specified, it must match a column in the primary table which is subject to a unique constraint or primary key constraint, and that constraint must consist of only that one column. Otherwise the foreign key references the primary key of the second table. Primary key and foreign key must have the same data type and the same precision, scale, and sign. Only a non unique single-column HG index is created for a single-column foreign key. For a multicolumn foreign key, SAP Sybase IQ creates a non unique composite HG index. The maximum width of a multicolumn composite key for a unique or non unique HG index is 1KB.

    A temporary table cannot have a foreign key that references a base table and a base table cannot have a foreign key that references a temporary table. Local temporary tables cannot have or be referenced by a foreign key.

  • FOREIGN KEY [role-name] [(...)] REFERENCES primary-table-name [(...)] – defines foreign-key references to a primary key or a unique constraint in another table. Normally, a foreign key would be for a primary key rather than an unique constraint. (In this description, this other table is called the primary table.)

    If the primary table column names are not specified, the primary table columns are the columns in the table's primary key. If foreign key column names are not specified, the foreign-key columns have the same names as the columns in the primary table. If foreign-key column names are specified, then the primary key column names must be specified, and the column names are paired according to position in the lists.

    If the primary table is not the same as the foreign-key table, either the unique or primary key constraint must have been defined on the referenced key. Both referenced key and foreign key must have the same number of columns, of identical data type with the same sign, precision, and scale.

    The value of the row's foreign key must appear as a candidate key value in one of the primary table's rows unless one or more of the columns in the foreign key contains nulls in a null allows foreign key column.

    Any foreign-key column not explicitly defined is automatically created with the same data type as the corresponding column in the primary table. These automatically created columns cannot be part of the primary key of the foreign table. Thus, a column used in both a primary key and foreign key must be explicitly created.

    role-name is the name of the foreign key. The main function of role-name is to distinguish two foreign keys to the same table. If no role-name is specified, the role name is assigned as follows:

    1. If there is no foreign key with a role-name the same as the table name, the table name is assigned as the role-name.
    2. If the table name is already taken, the role-name is the table name concatenated with a zero-padded 3-digit number unique to the table.

    The referential integrity action defines the action to be taken to maintain foreign-key relationships in the database. Whenever a primary key value is changed or deleted from a database table, there may be corresponding foreign key values in other tables that should be modified in some way. You can specify an ON DELETE clause, followed by the RESTRICT clause.

  • RESTRICT – generates an error if you try to update or delete a primary key value while there are corresponding foreign keys elsewhere in the database. Generates an error if you try to update a foreign key so that you create new values unmatched by a candidate key. This is the default action, unless you specify that LOAD optionally reject rows that violate referential integrity. This enforces referential integrity at the statement level.

    If you use CHECK ON COMMIT without specifying any actions, then RESTRICT is implied as an action for DELETE. SAP Sybase IQ does not support CHECK ON COMMIT.

    a global temporary table cannot have a foreign key that references a base table and a base table cannot have a foreign key that references a global temporary table. Local temporary tables cannot have or be referenced by a foreign key.

  • CHECK ( condition ) – no row is allowed to fail the condition. If an INSERT statement would cause a row to fail the condition, the operation is not permitted and the effects of the statement are undone.

    The change is rejected only if the condition is FALSE; in particular, the change is allowed if the condition is UNKNOWN. CHECK condition is not enforced by SAP Sybase IQ.

    Note: If possible, do not define referential integrity foreign key-primary key relationships in SAP Sybase IQ unless you are certain there are no orphan foreign keys.

• Remote Tables – foreign-key definitions are ignored on remote tables. Foreign-key definitions on local tables that refer to remote tables are also ignored. Primary-key definitions are sent to the remote server if the server supports it.

• PARTITION BY – divides large tables into smaller, more manageable storage objects. Partitions share the same logical attributes of the parent table, but can be placed in separate dbspaces and managed individually. SAP Sybase IQ supports several table partitioning schemes:
  • hash-partitions
  • range-partitions
  • composite-partitions
  A partition-key is the column or columns that contain the table partitioning keys. Partition keys can contain NULL and DEFAULT values, but cannot contain:

  • LOB (BLOB or CLOB) columns
  • BINARY, or VARBINARY columns
  • CHAR or VARCHAR columns whose length is over 255 bytes
  • BIT columns
  • FLOAT/DOUBLE/REAL columns

• PARTITION BY RANGE – partitions rows by a range of values in the partitioning column. Range partitioning is restricted to a single partition key column and a maximum of 1024 partitions. In a range-partitioning-scheme, the partition-key is the column that contains the table partitioning keys:

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

  The partition-name is the name of a new partition on which table rows are stored. Partition names must be unique within the set of partitions on a table. The partition-name is required.
  • VALUE – specifies the inclusive upper bound for each partition (in ascending order). The user must specify the partitioning criteria for each range partition to guarantee that each row is distributed to only one partition. NULLs are allowed for the partition column and rows with NULL as partition key value belong to the first table partition. However, NULL cannot be the bound value.

    There is no lower bound (MIN value) for the first partition. Rows of NULL cells in the first column of the partition key will go to the first partition. For the last partition, you can either specify an inclusive upper bound or MAX. If the upper bound value for the last partition is not MAX, loading or inserting any row with partition key value larger than the upper bound value of the last partition generates an error.

  • Max – denotes the infinite upper bound and can only be specified for the last partition.
  • IN – specifies the dbspace in the partition-decl on which rows of the partition should reside.
  These restrictions affect partitions keys and bound values for range partitioned tables:

  • Partition bounds must be constants, not constant expressions.
  • Partition bounds must be in ascending order according to the order in which the partitions were created. That is, the upper bound for the second partition must be higher than for the first partition, and so on.

    In addition, partition bound values must be compatible with the corresponding partition-key column data type. For example, VARCHAR is compatible with CHAR.

  • If a bound value has a different data type than that of its corresponding partition key column, SAP Sybase IQ converts the bound value to the data type of the partition key column, with these exceptions:
  • Explicit conversions are not allowed. This example attempts an explicit conversion from INT to VARCHAR and generates an error:

    CREATE TABLE Employees(emp_name VARCHAR(20)) 
    PARTITION BY RANGE(emp_name)
    (p1 VALUES <=(CAST (1 AS VARCHAR(20))), 
    p2 VALUES <= (CAST (10 AS VARCHAR(20)))

  • Implicit conversions that result in data loss are not allowed. In this example, the partition bounds are not compatible with the partition key type. Rounding assumptions may lead to data loss and an error is generated:

    CREATE TABLE emp_id (id INT) PARTITION BY RANGE(id) (p1 VALUES <= (10.5), p2 VALUES <= (100.5))

  • In this example, the partition bounds and the partition key data type are compatible. The bound values are directly converted to float values. No rounding is required, and conversion is supported:

    CREATE TABLE id_emp (id FLOAT)
    PARTITION BY RANGE(id) (p1 VALUES <= (10), 
    p2 VALUES <= (100))

  • Conversions from non-binary data types to binary data types are not allowed. For example, this conversion is not allowed and returns an error:

    CREATE TABLE newemp (name BINARY)
    PARTITION BY RANGE(name) 
    (p1 VALUES <= ("Maarten"), 
    p2 VALUES <= ("Zymmerman")

  • NULL cannot be used as a boundary in a range-partitioned table.
  • The row will be in the first partition if the cell value of the 1st column of the partition key evaluated to be NULL. SAP Sybase IQ supports only single column partition keys, so any NULL in the partition key distributes the row to the first partition.

• PARTITION BY HASH – maps data to partitions based on partition-key values processed by an internal hashing function. Hash partition keys are restricted to a maximum of eight columns with a combined declared column width of 5300 bytes or less. For hash partitions, the table creator determines only the partition key columns; the number and location of the partitions are determined internally.
  In a hash-partitioning declaration, the partition-key is a column or group of columns, whose composite value determines the partition where each row of data is stored:

  hash-partitioning-scheme: 
    HASH  ( partition-key [ , partition-key, … ] )

  • Restrictions –
    • You can only hash partition a base table. Attempting to partitioning a global temporary table or a local temporary table raises an error.
    • You cannot add, drop, merge, or split a hash partition.
    • You cannot add or drop a column from a hash partition key.
• PARTITION BY HASH RANGE – subpartitions a hash-partitioned table by range. In a hash-range-partitioning-scheme declaration, a SUBPARTITION BY RANGE clause adds a new range subpartition to an existing hash-range partitioned table:

  hash-range-partitioning-scheme:                
  PARTITION BY HASH  ( partition-key [ , partition-key, … ] )
      [ SUBPARTITION BY RANGE  ( range-partition-decl [ , range-partition-decl ... ] ) ]

  The hash partition specifies how the data is logically distributed and colocated; the range subpartition specifies how the data is physically placed. The new range subpartition is logically partitioned by hash with the same hash partition keys as the existing hash-range partitioned table. The range subpartition key is restricted to one column.
  • Restrictions –
    • You can only hash partition a base table. Attempting to partitioning a global temporary table or a local temporary table raises an error.
    • You cannot add, drop, merge, or split a hash partition.
    • You cannot add or drop a column from a hash partition key.
    Note: Range-partitions and composite partitioning schemes, like hash-range partitions, require the separately licensed VLDB Management option.

Examples

(back to top)

• Example 1 – create a table named SalesOrders2 with five columns. Data pages for columns FinancialCode, OrderDate, and ID are in dbspace Dsp3. Data pages for integer column CustomerID are in dbspace Dsp1. Data pages for CLOB column History are in dbspace Dsp2. Data pages for the primary key, HG for ID, are in dbspace Dsp4:

  CREATE TABLE SalesOrders2 (
  FinancialCode CHAR(2),
  CustomerID int IN Dsp1,
  History CLOB IN Dsp2,
  OrderDate TIMESTAMP,
  ID BIGINT,
  PRIMARY KEY(ID) IN Dsp4 
  ) IN Dsp3

• Example 2 – create a table fin_code2 with four columns. Data pages for columns code, type, and id are in the default dbspace, which is determined by the value of the database option DEFAULT_DBSPACE. Data pages for CLOB column description are in dbspace Dsp2. Data pages from foreign key fk1, HG for c1 are in dbspace Dsp4:

  CREATE TABLE fin_code2 (
  code INT,
  type CHAR(10),
  description CLOB IN Dsp2,
  id BIGINT,
  FOREIGN KEY fk1(id) REFERENCES SalesOrders(ID) IN Dsp4
  )

• Example 3 – create a table t1 where partition p1 is adjacent to p2 and partition p2 is adjacent to p3:

  CREATE TABLE t1 (c1 INT, c2 INT)
  PARTITION BY RANGE(c1) 
  (p1 VALUES <= (0), p2 VALUES <= (10), p3 VALUES <= (100))

• Example 4 – create a RANGE partitioned table bar with six columns and three partitions, mapping data to partitions based on dates:

  CREATE TABLE bar (
  		c1 INT IQ UNIQUE(65500),
  		c2 VARCHAR(20),
  		c3 CLOB PARTITION (P1 IN Dsp11, P2 IN Dsp12,
  		   P3 IN Dsp13),
  		c4 DATE,
  		c5 BIGINT,
  		c6 VARCHAR(500) PARTITION (P1 IN Dsp21,
  		   P2 IN Dsp22),
  		PRIMARY KEY (c5) IN Dsp2) IN Dsp1
  		PARTITION BY RANGE (c4)
  		(P1 VALUES <= ('2006/03/31') IN Dsp31, 
  		 P2 VALUES <= ('2006/06/30') IN Dsp32,
  		 P3 VALUES <= ('2006/09/30') IN Dsp33
  		) ;

  Data page allocation for each partition:

  Partition	Dbspaces	Columns
  P1	Dsp31	c1, c2, c4, c5
  P1	Dsp11	c3
  P1	Dsp21	c6
  P2	Dsp32	c1, c2, c4, c5
  P2	Dsp12	c3
  P2	Dsp22	c6
  P3	Dsp33	c1, c2, c4, c5, c6
  P3	Dsp13	c3
  P1, P2, P3	Dsp1	lookup store of c1 and other shared data
  P1, P2, P3	Dsp2	primary key (HG for c5)

• Example 5 – create a HASH partitioned (table tbl42) that includes a PRIMARY KEY (column c1) and a HASH PARTITION KEY (columns c4 and c3).

  CREATE TABLE tbl42 (
      c1 BIGINT NOT NULL,
      c2 CHAR(2) IQ UNIQUE(50),
      c3 DATE IQ UNIQUE(36524),
      c4 VARCHAR(200),
    PRIMARY KEY (c1)
      ) 
    PARTITION BY HASH ( c4, c3 )

• Example 6 – create a hash-ranged partitioned table with a PRIMARY KEY (column c1), a hash partition key (columns c4 and c2) and a range subpartition key (column c3).

  CREATE TABLE tbl42 (
      c1 BIGINT NOT NULL,
      c2 CHAR(2) IQ UNIQUE(50),
      c3 DATE,
      c4 VARCHAR(200),
    PRIMARY KEY (c1)) IN Dsp1
    
    PARTITION BY HASH ( c4, c2 )
    SUBPARTITION BY RANGE ( c3 )
    ( P1 VALUES <= (2011/03/31) IN Dsp31,
      P2 VALUES <= (2011/06/30) IN Dsp32,
      P3 VALUES <= (2011/09/30) IN Dsp33) ;

• Example 7 – create a table for a library database to hold information on borrowed books:

  CREATE TABLE borrowed_book (
  date_borrowed DATE NOT NULL,
  date_returned DATE,
  book       CHAR(20)
             REFERENCES library_books (isbn),
  CHECK( date_returned >= date_borrowed )
  )

• Example 8 – create table t1 at the remote server SERVER_A and create a proxy table named t1 that is mapped to the remote table:

  CREATE TABLE t1
  ( a  INT,
    b  CHAR(10))
  AT 'SERVER_A.db1.joe.t1'

• Example 9 – create table tab1 that contains a column c1 with a default value of the special constant LAST USER:

  CREATE TABLE tab1(c1 CHAR(20) DEFAULT LAST USER)

• Example 10 – create a local temporary table tab1 that contains a column c1:

  CREATE LOCAL TEMPORARY TABLE tab1(c1 int) IN IQ_SYSTEM_TEMP

  The example creates tab1 in the IQ_SYSTEM_TEMP dbspace in the following cases:

  • DQP_ENABLED logical server policy option is set ON but there are no read-write files in IQ_SHARED_TEMP
  • DQP_ENABLED option is OFF, TEMP_DATA_IN_SHARED_TEMP logical server policy option is ON, but there are no read-write files in IQ_SHARED_TEMP
  • Both the DQP_ENABLED option and the TEMP_DATA_IN_SHARED_TEMP option are set OFF
  The example creates the same table tab1 in the IQ_SHARED_TEMP dbspace in the following cases:

  • DQP_ENABLED is ON and there are read-write files in IQ_SHARED_TEMP
  • DQP_ENABLED is OFF, TEMP_DATA_IN_SHARED_TEMP is ON, and there are read-write files in IQ_SHARED_TEMP
• Example 11 – create a table tab1 that is enabled to use row-level versioning, and real-time storage in the in-memory RLV store.

  CREATE TABLE tab1 ( c1 INT, c2 CHAR(25) ) ENABLE RLV STORE

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.

Standards

(back to top)

Permissions

(back to top)