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

If the table being created is mapped to a remote location, a proxy table is created. A proxy table is identical to a local table, except that the sysobjects.sysstat2 column contains a status flag that indicates the table is mapped to an external location.
The external location must be previously defined using the at pathname clause.
After the Adaptive Server processes the create table command, it notifies Component Integration Services of the need to forward the command to the remote location (if a location has been previously specified).

Component Integration Services reconstructs the SQL necessary to create the table, and forwards the SQL to the remote server. It does not forward all the original syntax to the remote server. The following clauses are processed by Adaptive Server:
- on segment name
- check constraints
- default
- with max_rows_per_page
Trace flag 11213 changes the behavior of the create table command. Referential constraints and unique or primary key constraints are forwarded to the remote server unless trace flag 11213 is turned on, in which case they are processed locally.
For each column, the column name, datatype, length, identity property, and null property are reconstructed from the original statement.
Component Integration Services passes a NULL char column as a NULL varchar column.
Component Integration Services passes a NULL binary column as a NULL varbinary column.

Server Class ASEnterprise

Component Integration Services passes the datatype of each column to the remote server without conversion.

Server Class ASAnywhere

Component Integration Services passes the datatype of each column to the remote server without conversion.

Server Class ASIQ

Component Integration Services passes the datatype of each column to the remote server without conversion.

Server Class sql_server

Component Integration Services passes the datatype of each column to the remote server without conversion.

Server Class direct_connect

Component Integration Services reconstructs the create table command and passes commands to the targeted DirectConnect. The gateway transforms the commands into a form that the underlying DBMS recognizes.
Some DirectConnects support DB2 syntax mode, which is described in the DirectConnect documentation. When the DirectConnect enables DB2 syntax mode, Component Integration Services constructs DB2 SQL syntax and converts the column to a datatype DB2 supports.
Adaptive Server datatypes are converted to either the DirectConnect or DB2 syntax mode datatypes shown in Table 3-8, depending on whether the DirectConnect supports DB2 syntax mode

**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