create table

Description

Creates new tables and optional integrity constraints; specifies a locking scheme for the table being created; specifies ascending or descending index order when creating referential integrity constraints that depend on indexes; specifies the expected row size, to reduce row forwarding; specifies a ratio of empty pages to be left for each filled page; allows you to map the table to a table, view, or procedure at a remote location.

Syntax

 create table [database.[owner].]table_name (column_name datatype
    [default {constant_expression | user | null}]
    {[{identity | null | not null}]
	 	[off row | in row]
    | [[constraint constraint_name]
        {{unique | primary key}
       [clustered | nonclustered] [asc | desc]
       [with { { fillfactor = pct
                   | max_rows_per_page = num_rows }
                   , reservepagegap = num_pages }]
        [on segment_name]
        | references [[database.]owner.]ref_table
            [(ref_column)]
        | check (search_condition)}]}...
	| [constraint constraint_name]
    {{unique | primary key} 
        [clustered | nonclustered]
        (column_name [asc | desc]
			[{, column_name [asc | desc]}...])
       [with { {fillfactor = pct 
			 | max_rows_per_page = num_rows },
			reservepagegap = num_pages } ]
         [on segment_name]
    |foreign key (column_name [{, column_name}...])
        references [[database.]owner.]ref_table
            [(ref_column [{, ref_column}...])]
     | check (search_condition) ... }
[{, {next_column | next_constraint}}...])
[lock {datarows | datapages | allpages }]
[with { max_rows_per_page = num_rows , 
		exp_row_size = num_bytes , 
		reservepagegap = num_pages } ] 
[on segment_name]
[ [ external {table | file}] at “pathname” ]]

Usage

Usage


Server Class ASEnterprise


Server Class ASAnywhere


Server Class ASIQ


Server Class sql_server


Server Class direct_connect

Table 3-8: DirectConnect datatype conversions for create table

Adaptive Server Datatype

DirectConnect Default Datatype

DirectConnect DB2 Syntax Mode Datatype

binary(n)

binary(n)

char(n) for bit data

bit

bit

char(1)

char

char

char

datetime

datetime

timestamp

decimal(p, s)

decimal(p, s)

decimal(p, s)

float

float

float

image

image

varchar(n) for bit data; the value of n is determined by the global variable @@textsize

int

int

int

money

money

float

numeric(p, s)

numeric(p, s)

decimal(p, s)

nchar(n)

nchar(n)

graphic(n)

nvarchar(n)

nvarchar(n)

vargraphic(n)

real

real

real

smalldatetime

smalldatetime

timestamp

smallint

smallint

smallint

smallmoney

smallmoney

float

timestamp

timestamp

varbinary(8)

tinyint

tinyint

smallint

text

text

varchar(n); the value of n is determined by the global variable @@textsize

unichar(n)

unichar

char(n) for bit data

univarchar(n)

char(n) for bit data

varchar(n) for bit data

varbinary(n)

varbinary(n)

varchar(n) for bit data

varchar(n)

varchar(n)

varchar(n)


Server Class db2

Table 3-9 shows the datatype conversions that are performed when a create table command is processed. Adaptive Server datatypes are converted to the DB2 datatypes shown.

Table 3-9: DB2 datatype conversions for create table

Adaptive Server Datatype

DB2 Datatype

binary(n)

char(n) for bit data, where n <= 254

bit

char(1)

char(n)

char(n), where n <= 254

datetime

timestamp

decimal(p, s)

decimal(p, s)

float

float

image

Not supported

int

int

money

float

nchar

char(n)

nvarchar

varchar(n)

numeric(p, s)

decimal(p, s)

real

real

smalldatetime

timestamp

smallint

smallint

smallmoney

float

tinyint

smallint

text

Not supported

varbinary(n)

varchar(n) for bit data, where n <=254

varchar(n)

varchar(n), where n <= 254

See also

See Also

create table in the Adaptive Server Reference Manual.