SET Statement [T-SQL]

Sets database options in an Adaptive Server Enterprise-compatible manner.

Syntax

SET option-name option-value

Usage

Database options in SAP Sybase IQ are set using the SET OPTION statement. However, SAP Sybase IQ also provides support for the Adaptive Server Enterprise SET statement for a set of options particularly useful for compatibility.

Transact-SQL SET Options
Option name	Option value
ANSINULL	ON \| OFF
ANSI_PERMISSIONS	ON \| OFF
CLOSE_ON_ENDTRANS	ON
QUOTED_IDENTIFIER	ON \| OFF
ROWCOUNT	`integer`
STRING_RTRUNCATION	ON \| OFF
TRANSACTION ISOLATION LEVEL	0 \| 1 \| 2 \| 3

You can set these options using the Transact-SQL SET statement in SAP Sybase IQ, as well as in Adaptive Server Enterprise:

SET ANSINULL { ON | OFF } – the default behavior for comparing values to NULL in SAP Sybase IQ and Adaptive Server Enterprise is different. Setting ANSINULL to OFF provides Transact-SQL compatible comparisons with NULL.
SET ANSI_PERMISSIONS { ON | OFF } – the default behavior in SAP Sybase IQ and Adaptive Server Enterprise regarding permissions required to carry out a DELETE containing a column reference is different. Setting ANSI_PERMISSIONS to OFF provides Transact-SQL-compatible permissions on DELETE.
SET CLOSE_ON_ENDTRANS { ON } – when CLOSE_ON_ENDTRANS is set to ON (the default and only allowable value), cursors are closed at the end of a transaction. With the option set ON, CLOSE_ON_ENDTRANS provides Transact-SQL-compatible behavior.
SET QUOTED_IDENTIFIER { ON | OFF } – controls whether strings enclosed in double quotes are interpreted as identifiers (ON) or as literal strings (OFF).
SET ROWCOUNT integer – the Transact-SQL ROWCOUNT option limits to the specified integer the number of rows fetched for any cursor. This includes rows fetched by repositioning the cursor. Any fetches beyond this maximum return a warning. The option setting is considered when returning the estimate of the number of rows for a cursor on an OPEN request.
Note: SAP Sybase IQ supports the @@rowcount global variable. SELECT, INSERT, DELETE, and UPDATE statements affect the value of the ROWCOUNT option. The ROWCOUNT option has no effect on cursor operation, the IF statement, or creating/dropping a table or procedure.

In SAP Sybase IQ, if ROWCOUNT is greater than the number of rows that dbisql can display, dbisql may do extra fetches to reposition the cursor. The number of rows actually displayed may be less than the number requested. Also, if any rows are refetched due to truncation warnings, the count might be inaccurate.

A value of zero resets the option to get all rows.
SET STRING_RTRUNCATION { ON | OFF } – the default behavior in SAP Sybase IQ and Adaptive Server Enterprise when nonspace characters are truncated on assigning SQL string data is different. Setting STRING_RTRUNCATION to ON provides Transact-SQL-compatible string comparisons, including hexadecimal string (binary data type) comparisons.
SET TRANSACTION ISOLATION LEVEL { 0 | 1 | 2 | 3 } – sets the locking isolation level for the current connection For Adaptive Server Enterprise, only 1 and 3 are valid options. For SAP Sybase IQ, only 3 is a valid option.
SET PREFETCH { ON | OFF } – this SET statement is allowed by SAP Sybase IQ for compatibility, but has no effect:

Standards

SQL—Transact-SQL extension to ISO/ANSI SQL grammar.
Sybase—SAP Sybase IQ supports a subset of the Adaptive Server Enterprise database options.

Permissions

None