SET Statement [T-SQL]

Syntax

SET option-name option-value

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 ]

Parameters

• ANSINULL – 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.

• ANSI_PERMISSIONS – 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.

• CLOSE_ON_ENDTRANS – when 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.

• QUOTED_IDENTIFIER – controls whether strings enclosed in double quotes are interpreted as identifiers (ON) or as literal strings (OFF).

• ROWCOUNT – in the Transact-SQL, 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 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 clause. The ROWCOUNT clause has no effect on cursor operation, the IF statement, or creating or 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.

• STRING_RTRUNCATION – 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.

• TRANSACTION ISOLATION LEVEL – 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 – is allowed by SAP Sybase IQ for compatibility, but has no effect.

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.

Standards

Permissions

None