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

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » SQL Dialects and Compatibility » Other SQL dialects » Writing compatible SQL statements


Writing compatible queries

There are two criteria for writing a query that runs on both SQL Anywhere and Adaptive Server Enterprise databases:

  • The data types, expressions, and search conditions in the query must be compatible.
  • The syntax of the SELECT statement itself must be compatible.

This section explains compatible SELECT statement syntax, and assumes compatible data types, expressions, and search conditions. The examples assume the quoted_identifier setting is Off: the default Adaptive Server Enterprise setting, but not the default SQL Anywhere setting.

SQL Anywhere supports the following subset of the Transact-SQL SELECT statement:

SELECT [ ALL | DISTINCT ] select-list
...[ INTO #temporary-table-name ]
...[ FROM table-spec [ HOLDLOCK | NOHOLDLOCK ],
...  table-spec [ HOLDLOCK | NOHOLDLOCK ], ... ]
...[ WHERE search-condition ]
...[ GROUP BY column-name, ... ]
...[ HAVING search-condition ]
   [ ORDER BY { expression | integer }
      [ ASC | DESC ], ... ]
|  *
| expression
| alias-name = expression
| expression as identifier  
| expression as string
[ owner . ]table-name
...[ [ AS ] correlation-name ]
 ...[ ( INDEX index_name [ PREFETCH size  ][ LRU | MRU ] ) ]
identifier | 'string' | "string"

SQL Anywhere does not support the following keywords and clauses of the Transact-SQL SELECT statement syntax:

  • SQL Anywhere does not support the Transact-SQL extension to the GROUP BY clause allowing references to columns and expressions that are not used for creating groups. In Adaptive Server Enterprise, this extension produces summary reports.
  • The performance parameters part of the table specification is parsed, but has no effect.
  • The HOLDLOCK keyword is supported by SQL Anywhere. It makes a shared lock on a specified table or view more restrictive by holding it until the completion of a transaction (instead of releasing the shared lock as soon as the required data page is no longer needed, whether or not the transaction has been completed). For the purposes of the table for which the HOLDLOCK is specified, the query is performed at isolation level 3.
  • The HOLDLOCK option applies only to the table or view for which it is specified, and only for the duration of the transaction defined by the statement in which it is used. Setting the isolation level to 3 applies a holdlock for each select within a transaction. You cannot specify both a HOLDLOCK and NOHOLDLOCK option in a query.
  • The NOHOLDLOCK keyword is recognized by SQL Anywhere, but has no effect.
  • Transact-SQL uses the SELECT statement to assign values to local variables:
    SELECT @localvar = 42;

    The corresponding statement in SQL Anywhere is the SET statement:

    SET @localvar = 42;
  • Adaptive Server Enterprise does not support the following clauses of the SELECT statement syntax:
    • INTO host-variable-list
    • INTO variable-list
    • Parenthesized queries
  • Adaptive Server Enterprise uses join operators in the WHERE clause, rather than the FROM clause and the ON condition for joins.
See also