Reference Manual
   CHAPTER 9. SQL Statements     

SET statement [T-SQL]

Function 

To set database options for the current connection, in an Adaptive Server Enterprise-compatible manner.

Syntax 

SET option-name option-value

Permissions 

None.

Side effects 

None.

See also 

SET OPTION statement

Description 

The available options are as follows:

Option name	Option value
ANSINULL	ON | OFF
ANSI_PERMISSIONS	ON | OFF
CLOSE_ON_ENDTRANS	ON | OFF
QUOTED_IDENTIFIER	ON | OFF
ROWCOUNT	integer
SELF_RECURSION	ON | OFF
STRING_RTRUNCATION	ON | OFF
TEXTSIZE	integer
TRANSACTION_ISOLATION_LEVEL	0 | 1 | 2 | 3

Database options in Adaptive Server Anywhere are set using the SET OPTION statement. However, Adaptive Server Anywhere also provides support for the Adaptive Server Enterprise SET statement for options that are particularly useful for compatibility.

The SET statement sets the option for the duration of the current connection only. It is equivalent to SET TEMPORARY OPTION.

The following options can be set using the Transact-SQL SET statement in Adaptive Server Anywhere as well as in Adaptive Server Enterprise:

• SET ANSINULL { ON | OFF }     The default behavior for comparing values to NULL in Adaptive Server Anywhere 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 Adaptive Server Anywhere and Adaptive Server Enterprise regarding permissions required to carry out an UPDATE or DELETE containing a column reference is different. Setting ANSI_PERMISSIONS to OFF provides Transact-SQL-compatible permissions on UPDATE and DELETE.

• SET CLOSE_ON_ENDTRANS { ON | OFF }     The default behavior in Adaptive Server Anywhere and Adaptive Server Enterprise for closing cursors at the end of a transaction is different. Setting CLOSE_ON_ENDTRANS to OFF 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). For information about this option, see Setting options for Transact-SQL compatibility.

• SET ROWCOUNT     integer The Transact-SQL ROWCOUNT option limits the number of rows fetched for any cursor to the specified integer. This includes rows fetched by re-positioning 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.

  SET ROWCOUNT also limits the number of rows affected by a searched UPDATE or DELETE statement to integer. This might be used, for example, to allow COMMIT statements to be performed at regular intervals to limit the size of the rollback log and lock table. The application (or procedure) would need to provide a loop to cause the update/delete to be re-issued for rows that are not affected by the first operation. A simple example is given below:

  begin
     declare @count integer
  
     set rowcount 20
  
     while(1=1) begin
        update employee set emp_lname='new_name'
        where emp_lname <> 'old_name'
  
        /* Stop when no rows changed */
        select @count = @@rowcount
        if @count = 0 break
        print string('Updated ', 
                 @count,' rows; repeating...')
        commit
     end
  
     set rowcount 0
  end

  In Adaptive Server Anywhere, if the ROWCOUNT setting is greater than the number of rows that Interactive SQL can display, Interactive SQL 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 re-fetched due to truncation warnings, the count may be inaccurate.

  A value of zero resets the option to get all rows.

• SET SELF_RECURSION { ON | OFF }     The self_recursion option is used within triggers to enable (ON) or prevent (OFF) operations on the table associated with the trigger from firing other triggers.

• SET STRING_RTRUNCATION { ON | OFF }     The default behavior in Adaptive Server Anywhere and Adaptive Server Enterprise when non-space characters are truncated on assigning SQL string data is different. Setting STRING_RTRUNCATION to ON provides Transact-SQL-compatible string comparisons.

• SET TEXTSIZE     Specifies the maximum size (in bytes) of text or image type data to be returned with a select statement. The @@textsize global variable stores the current setting. To reset to the default size (32K), which is also the maximum setting, use the command:

  set textsize 0

• SET TRANSACTION-ISOLATION-LEVEL { 0 | 1 | 2 | 3 }     Sets the locking isolation level for the current connection, as described in Isolation levels and consistency. For Adaptive Server Enterprise, only 1 and 3 are valid options. For Adaptive Server Anywhere, any of 0, 1, 2, or 3 is a valid option.

In addition, the following SET statement is allowed by Adaptive Server Anywhere for compatibility, but has no effect:

• SET PREFETCH {ON | OFF}    

Standards and compatibility 

• SQL/92     Transact-SQL extension

• Sybase     Adaptive Server Anywhere supports a subset of the Adaptive Server Enterprise database options.