Transact-SQL compatibility options

The following options allow Sybase IQ behavior to be compatible with Adaptive Server Enterprise, or to both support old behavior and allow ISO SQL92 behavior.

For further compatibility with Adaptive Server Enterprise, you can set some of these options set for the duration of the current connection using the Transact-SQL SET statement instead of the Sybase IQ SET OPTION statement. For a listing of such options, see the SET statement [ESQL].

Default settings

The default setting for some of these options differs from the Adaptive Server Enterprise default setting. To ensure compatible behavior, you should explicitly set the options.

When a connection is made using the Open Client or JDBC interfaces, some option settings are explicitly set for the current connection to be compatible with Adaptive Server Enterprise. These options are listed in Table 2-2.

For information on how the settings are made, see Reference: Building Blocks, Tables, and Procedures.

Table 2-2: Transact-SQL options set explicitly for ASE compatibility

Option

ASE-compatible setting

ALLOW_NULLS_BY_DEFAULT

OFF

ANSINULL

OFF

CHAINED

OFF

CONTINUE_AFTER_RAISERROR

ON

DATE_FORMAT

YYYY-MM-DD

DATE_ORDER

MDY

ESCAPE_CHARACTER

OFF

ISOLATION_LEVEL

1

ON_TSQL_ERROR

CONDITIONAL

QUOTED_IDENTIFIER

OFF

TIME_FORMAT

HH:NN:SS.SSS

TIMESTAMP_FORMAT

YYYY-MM-DD HH:NN:SS.SSS

TSQL_VARIABLES

OFF

List of options

Table 2-3 lists the compatibility options, their allowed values, and their default settings.

See “General database options” and “DBISQL options” for lists of the other classes of options.

Table 2-3: Transact-SQL compatibility options

Option

Values

Default

ALLOW_NULLS_BY_DEFAULT

ON, OFF

ON

ANSI_BLANKS*

ON, OFF

OFF

ANSI_CLOSE_CURSORS_ON_ROLLBACK

ON

ON

ANSI_INTEGER_OVERFLOW*

ANSI_PERMISSIONS

ON, OFF

ON

ANSINULL

ON, OFF

ON

ANSI_UPDATE_CONSTRAINTS

OFF, CURSORS, STRICT

CURSORS

ASE_BINARY_DISPLAY

ON, OFF

OFF

ASE_FUNCTION_BEHAVIOR

ON, OFF

OFF

CHAINED

ON, OFF

ON

CLOSE_ON_ENDTRANS

ON

ON

CONTINUE_AFTER_RAISERROR

ON, OFF

ON

CONVERSION_ERROR

ON, OFF

ON

ESCAPE_CHARACTER*

Reserved

Reserved

FIRE_TRIGGERS*

ON, OFF

ON

NEAREST_CENTURY

0 – 100

50

NON_KEYWORDS

Comma-separated keywords list

No keywords turned off

ON_TSQL_ERROR

STOP, CONTINUE, CONDITIONAL

CONDITIONAL

QUERY_PLAN_ON_OPEN*

QUOTED_IDENTIFIER

ON, OFF

ON

RI_TRIGGER_TIME*

SQL_FLAGGER_ERROR_LEVEL

E, I, F, W

W

SQL_FLAGGER_WARNING_LEVEL

E, I, F, W

W

STRING_RTRUNCATION

ON, OFF

ON

TEXTSIZE*

TSQL_HEX_CONSTANT*

TSQL_VARIABLES

ON, OFF

OFF

NoteNotes An asterisk (*) next to the option name in Table 2-3 indicates an option currently not supported by Sybase IQ.