Queries that are compatible with Transact-SQL

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 statement itself must be compatible.

This section assumes compatible data types, expressions, and search conditions. The examples assume the quoted_identifier option is set to OFF, which is the default Adaptive Server Enterprise setting, but not the default SQL Anywhere setting.

SQL Anywhere's implementation of the Transact-SQL dialect supports much of the query expression syntax from the Watcom SQL dialect, even though some of these SQL constructions are not supported by Adaptive Server Enterprise. In a Transact-SQL query, SQL Anywhere supports the following SQL constructions:

the back quote character `, the double quote character ", and square parentheses [] to denote identifiers
UNION, EXCEPT, and INTERSECT query expressions
derived tables
table functions
CONTAINS table expressions for full text search
REGEXP, SIMILAR, IS DISTINCT FROM, and CONTAINS predicates
user-defined SQL or external functions
LEFT, RIGHT and FULL outer joins
GROUP BY ROLLUP, CUBE, and GROUPING SETS
TOP N START AT M
window aggregate functions and other analytic functions including statistical analysis and linear regression functions

To summarize, SQL Anywhere's Transact-SQL dialect supports the following:

Syntax

query-expression:
{ query-expression EXCEPT [ ALL ] query-expression
| query-expression INTERSECT [ ALL ] query-expression
| query-expression UNION [ ALL ] query-expression
| query-specification }
[ ORDER BY { expression | integer }
      [ ASC | DESC ], ... ]
[ FOR READ ONLY | for-update-clause ]
[ FOR XML xml-mode ]

query-specification:
SELECT [ ALL | DISTINCT ] [ cursor-range ] select-list
[ INTO #temporary-table-name ]
[ FROM  table-expression, ... ]
[ WHERE search-condition ]
[ GROUP BY group-by-term, ... ]
[ HAVING search-condition ]
[ WINDOW window-specification, ... ]

Parameters

select-list:
 table-name.*
|  *
| expression
| alias-name = expression
| expression as identifier  
| expression as string

table-expression: See FROM clause.

group-by-term: See GROUP BY clause.

for-update-clause: See FOR UPDATE or FOR READ ONLY clause, SELECT statement.

xml-mode: See SELECT statement.

alias-name:
identifier | 'string' | "string" | `string`

cursor-range:
{ FIRST | TOPconstant-or-variable } [ START AT constant-or-variable ]

Transact-SQL-table-reference:
[ owner .]table-name [ [ AS ] correlation-name ]
[ ( INDEX index_name [ PREFETCH size  ][ LRU | MRU ] ) ]

Notes

In addition to the Watcom SQL syntax for the FROM clause, SQL Anywhere supports Transact-SQL syntax for specific Adaptive Server Enterprise table hints. For a table reference, Transact-SQL-table-reference supports the INDEX hint keyword, along with the PREFETCH, MRU and LRU caching hints. PREFETCH, MRU and LRU are ignored in SQL Anywhere.
SQL Anywhere does not support the Transact-SQL extension to the GROUP BY clause allowing references to columns that are not included in the GROUP BY clause.

SQL Anywhere also does not support the Transact-SQL GROUP BY ALL construction.
SQL Anywhere supports a subset of Transact-SQL outer join constructions using the comparison operators *= and =*.
SQL Anywhere's Transact-SQL dialect does not support common table expressions except when embedded within a derived table. Consequently SQL Anywhere's Transact-SQL dialect does not support recursive UNION queries. Use the Watcom SQL dialect if you require this functionality.
The performance parameters part of the table specification is parsed, but has no effect.
The HOLDLOCK keyword is supported by SQL Anywhere. With HOLDLOCK, a shared lock on a specified table or view is more restrictive because the shared lock is not released when the data page is no longer needed. The query is performed at isolation level 3 on a table on which the HOLDLOCK is specified.
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:
- SELECT ... INTO host-variable-list
- SELECT ... INTO variable-list
- EXCEPT [ALL] or INTERSECT [ALL]
- START AT clause
- SQL Anywhere-defined table hints
- table functions
- FULL OUTER JOIN
- FOR UPDATE BY { LOCK | TIMESTAMP }
- window aggregate functions and linear regression functions
SQL Anywhere does not support the following keywords and clauses of the Adaptive Server Enterprise Transact-SQL SELECT statement syntax:
- SHARED keyword
- PARTITION keyword
- COMPUTE clause
- FOR BROWSE clause
- GROUP BY ALL clause
- PLAN clause
- ISOLATION clause