Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

SET statement [T-SQL]

Sets database options for the current connection in an Adaptive Server Enterprise-compatible manner.

SET option-name option-value

The available options are as follows:

Option name Option value
ansinull On or Off
ansi_permissions On or Off
close_on_endtrans On or Off

1, 2, 3, 4, 5, 6, or 7

The setting of this option affects the DATEPART function when obtaining a weekday value.

quoted_identifier On | Off
rowcount integer
self_recursion On | Off
string_rtruncation On | Off
textsize integer
transaction isolation level 0, 1, 2, 3, snapshot, statement snapshot, or read only statement snapshot

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

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

  • SET ansinull

    The default behavior for comparing values to NULL is different in SQL Anywhere and Adaptive Server Enterprise. Setting ansinull to Off provides Transact-SQL compatible comparisons with NULL.

    SQL Anywhere also supports the following syntax:

    SET ansi_nulls { On | Off }
  • SET ansi_permissions

    The default behavior is different in SQL Anywhere and Adaptive Server Enterprise regarding privileges required to perform an UPDATE or DELETE containing a column reference. Setting ansi_permissions to Off provides Transact-SQL-compatible privileges on UPDATE and DELETE.

  • SET close_on_endtrans

    The default behavior is different in SQL Anywhere and Adaptive Server Enterprise for closing cursors at the end of a transaction. Setting close_on_endtrans to Off provides Transact-SQL compatible behavior.

  • SET datefirst

    The default is 7, which means that the first day of the week is by default Sunday.

  • SET quoted_identifier

    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 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:

       DECLARE @count INTEGER
       SET rowcount 20
       WHILE(1=1) BEGIN
          UPDATE GROUPO.Employees SET Surname='new_name'
          WHERE Surname <> 'old_name'
          /* Stop when no rows changed */
          SELECT @count = @@rowcount
          IF @count = 0 BREAK
          PRINT string('Updated ',
                   @count,' rows; repeating...')
       SET rowcount 0

    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. So, 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

    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

    The default behavior is different between SQL Anywhere and Adaptive Server Enterprise when non-space characters are truncated during assignment of SQL string data. 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 (32 KB), use the command:

  • SET transaction isolation level

    Sets the locking isolation level for the current connection.

    For Adaptive Server Enterprise, only 1 and 3 are valid options. For SQL Anywhere, any of 0, 1, 2, 3, snapshot, statement snapshot, and read only statement snapshot is a valid option.

The SET statement is allowed by SQL Anywhere for the prefetch option, for compatibility, but has no effect.



Side effects


  • ANSI/ISO SQL Standard

    Not in the standard.