Show TOC

SET Statement [T-SQL]Locate this document in the navigation structure

Sets database options in an SAP ASE-compatible manner.

Quick Links:

Go to Parameters

Go to Usage

Go to Standards

Go to Permissions

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

(back to top)

  • ANSINULL the default behavior for comparing values to NULL in SAP IQ and SAP ASE is different. Setting ANSINULL to OFF provides Transact-SQL compatible comparisons with NULL.
  • ANSI_PERMISSIONS the default behavior in SAP IQ and SAP ASE 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 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 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 IQ and SAP ASE 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 SAP ASE, only 1 and 3 are valid options. For SAP IQ, only 3 is a valid option.
  • SET PREFETCH is allowed by SAP IQ for compatibility, but has no effect.
Usage

(back to top)

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

Standards

(back to top)

  • SQL—Transact-SQL extension to ISO/ANSI SQL grammar.
  • SAP Database products—SAP IQ supports a subset of the SAP ASE database options.
Permissions
Related Information