Considerations When Using the CREATE TABLE Statement

When creating tables for compatibility, be aware of the following compatibility considerations for NULL treatment, check constraints, referential integrity, default values, identify columns, computed columns, temporary tables, and table location.

NULL in Columns

For compatible treatment of NULL:

SQL Anywhere and SAP Sybase IQ assume that columns can be null unless NOT NULL is stated in the column definition. You can change this behavior by setting the database option ALLOW_NULLS_BY_DEFAULT to the Transact-SQL compatible setting of OFF.
SQL Anywhere and SAP Sybase IQ assume that BIT columns cannot be NULL.
Adaptive Server assumes that columns cannot be null unless NULL is stated.

Check Constraints

SAP Sybase IQ enforces check constraints on base, global temporary, and local temporary tables, and on user-defined data types. Users can log check integrity constraint violations and specify the number of violations that can occur before a LOAD statement rolls back.

SAP Sybase IQ does not allow the creation of a check constraint that it cannot evaluate, such as those composed of user-defined functions, proxy tables, or non-SAP Sybase IQ tables. Constraints that cannot be evaluated are detected the first time the table on which the check constraint is defined is used in a LOAD, INSERT, or UPDATE statement. SAP Sybase IQ does not allow check constraints containing:

Subqueries
Expressions specifying a host language parameter, a SQL parameter, or a column as the target for a data value
Set functions
Invocations of nondeterministic functions or functions that modify data

Adaptive Server and SQL Anywhere enforce CHECK constraints. SQL Anywhere allows subqueries in check constraints.

SAP Sybase IQ supports user-defined data types that allow constraints to be encapsulated in the data type definition.

Referential Integrity Constraints

Actions for enforcing integrity are supported as follows:

SQL Anywhere supports all ANSI actions: SET NULL, CASCADE, DEFAULT, RESTRICT.
Adaptive Server supports two of these actions: SET NULL, DEFAULT.
Note: You can achieve CASCADE in Adaptive Server by using triggers instead of referential integrity.
SAP Sybase IQ supports the RESTRICT action only.
SAP Sybase IQ does not support NOT NULL FOREIGN KEY.
SAP Sybase IQ has the restriction that a column cannot be both a candidate key and a foreign key at the same time.

Default Values in a Column

Default value support differs as follows:

Adaptive Server and SQL Anywhere support specifying a default value for a column.
Only SQL Anywhere supports DEFAULT UTC TIMESTAMP.
SAP Sybase IQ supports specifying a default value for a column, except for the special values DEFAULT UTC TIMESTAMP and DEFAULT CURRENT UTC TIMESTAMP. SAP Sybase IQ also ignores settings for the DEFAULT_TIMESTAMP_INCREMENT database option.

Identity Columns

Identity column support differs as follows:

SAP Sybase IQ supports IDENTITY or DEFAULT AUTOINCREMENT as a default value. SAP Sybase IQ supports identity columns of any numeric type with any precision and scale 0, and the column can be NULL. SAP Sybase IQ identity columns must be positive and are limited by the range of the data type. SAP Sybase IQ supports a single identity column per table, and requires database option IDENTITY_INSERT set to a table name for explicit inserts and updates. To drop a table with an IDENTITY column, you cannot have IDENTITY_INSERT set to that table. The table can contain data when adding an identity column. Tables derived using SELECT INTO do not have Identity/Autoincrement columns. SAP Sybase IQ views cannot contain IDENTITY/DEFAULT AUTOINCREMENT columns.
SQL Anywhere supports the AUTOINCREMENT default value. SQL Anywhere supports identity columns of any numeric type with any allowable scale and precision. The identity column value can be positive, negative, or zero, limited by the range of the data type. SQL Anywhere supports any number of identity columns per table, and does not require identity_insert for explicit inserts, drops, and updates. The table must be empty when adding identity columns. SQL Anywhere identity columns can be altered to be nonidentity columns, and vice versa. You can add or drop AUTOINCREMENT columns from SQL Anywhere views.
Adaptive Server supports a single identity column per table. Adaptive Server identity columns are restricted to only numeric data type scale 0, maximum precision 38. They must be positive, are limited by the range of the data type, and cannot be null. Adaptive Server requires identity_insert for explicit inserts and drops, but not for updates to the identity column. The table can contain data when you add an identity column. Adaptive Server users cannot explicitly set the next value chosen for an identity column. Adaptive Server views cannot contain IDENTITY/AUTOINCREMENT columns. When using SELECT INTO under certain conditions, Adaptive Server allows Identity/Autoincrement columns in the result table if they were in the table being selected from.

Computed Columns

Computed column support differs as follows:

SQL Anywhere supports computed columns that can be indexed.
Adaptive Server and SAP Sybase IQ do not.

Temporary Tables

You can create a temporary table by placing a pound sign (#) without an owner specification in front of the table name in a CREATE TABLE statement. These temporary tables are SAP Sybase IQ-declared temporary tables and are available only in the current connection.

Locating Tables

Physical placement of a table is carried out differently in Adaptive Server and SAP Sybase IQ. SAP Sybase IQ supports the ON segment-name clause, but segment-name refers to a SAP Sybase IQ dbspace.

Output for sp_iqstatus procedure

Sybase IQ (TM) Copyright (c) 1992-2013 by SAP AG or an SAP affiliate company. All rights reserved.
 Version: 16.0.0.562/130821/P/Mainline/Sun_x64/OS 5.10/64bit/2013-08-21 06:15:41
 Time Now: 2013-08-21 06:27:14.150
 Build Time: 2013-08-21 06:15:41
 File Format: 23 on 03/18/1999 
 Server mode: IQ Server
 Catalog Format: 2
 Stored Procedure Revision: 1
 Page Size: 65536/4096blksz/16bpp
 Number of Main DB Files: 2
 Main Store Out Of Space: N
 Number of Cache Dbspace Files: 5
 Number of Shared Temp DB Files: 0
 Shared Temp Store Out Of Space: N
 Number of Local Temp DB Files: 1
 Local Temp Store Out Of Space: N
 DB Blocks: 1-25600 IQ_SYSTEM_MAIN
 DB Blocks: 522208-547807 MainUser
 Cache Dbspace Blocks: 1-5120 ssd_dev_1
 Cache Dbspace Blocks: 522208-527327 ssd_dev_2
 Cache Dbspace Blocks: 1044416-1049535 ssd_dev_3
 Cache Dbspace Blocks: 1566624-1571743 ssd_dev_4
 Cache Dbspace Blocks: 2088832-2093951 ssd_dev_5
 Local Temp Blocks: 1-25600 IQ_SYSTEM_TEMP
 Create Time: 2013-08-21 06:27:05.444
 Update Time: 2013-08-21 06:27:14.035
 Main IQ Buffers: 1588, 100Mb
 Temporary IQ Buffers: 1588, 100Mb
 Main IQ Blocks Used: 5250 of 38400, 13%=20Mb, Max Block#: 5313
 Cache Dbspace IQ Blocks Used: 197 of 25600, 0%=0Mb, Max Block#: 0
 Shared Temporary IQ Blocks Used: 0 of 0, 0%=0Mb, Max Block#: 0
 Local Temporary IQ Blocks Used: 65 of 12800, 0%=0Mb, Max Block#: 0
 Main Reserved Blocks Available: 12800 of 12800, 100%=50Mb
 Shared Temporary Reserved Blocks Available: 0 of 0, 0%=0Mb
 Local Temporary Reserved Blocks Available: 12800 of 12800, 100%=50Mb
 IQ Dynamic Memory: Current: 292mb, Max: 308mb
 Main IQ Buffers: Used: 18, Locked: 0
 Temporary IQ Buffers: Used: 4, Locked: 0
 Main IQ I/O: I: L459/P9 O: C21/D33/P22 D:1 C:100.0
 Temporary IQ I/O: I: L320/P0 O: C54/D59/P8 D:50 C:100.0
 Other Versions: 0 = 0Mb
 Active Txn Versions: 0 = C:0Mb/D:0Mb
 Last Full Backup ID: 0
 Last Full Backup Time: 
 Last Backup ID: 0
 Last Backup Type: None
 Last Backup Time: 
 DB Updated: 0
 Blocks in next ISF Backup: 0 Blocks: =0Mb
 Blocks in next ISI Backup: 0 Blocks: =0Mb
 IQ large memory space: 2048Mb
 IQ large memory flexible percentage: 50
 IQ large memory flexible used: 0Mb
 IQ large memory inflexible percentage: 90
 IQ large memory inflexible used: 0Mb
 IQ large memory anti-starvation percentage: 50
DB File Encryption Status: OFF
 RLV memory limit: 2048Mb
 RLV memory used: 0Mb