SET statement [T-SQL]

Description

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

Syntax

SET option-name option-value

Usage

Table 1-13 lists available options.

**Table 1-13: 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

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

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

SET ANSINULL { ON | OFF } The default behavior for comparing values to NULL in 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 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.

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 Sybase IQ, if ROWCOUNT is greater than the number of rows that dbisql can display, dbisql may do some extra fetches to reposition the cursor. Thus, 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.