Collection Contents Index Configuring databases for Transact-SQL compatibility Transact-SQL procedure language overview pdf/chap30.pdf

User's Guide
   PART 5. The Adaptive Server Family
     CHAPTER 30. Transact-SQL Compatibility       

Writing compatible SQL statements

This section describes some issues of compatibility between Adaptive Server Enterprise and Adaptive Server Anywhere at the SQL statement level.

Top of page  Creating compatible tables

Adaptive Server Anywhere does not support named constraints or named defaults, but does support user-defined data types which allow constraint and default definitions to be encapsulated in the data type definition. It also supports explicit defaults and CHECK conditions in the CREATE TABLE statement.

Temporary tables 

You can create a temporary table by preceding the table name in a CREATE TABLE statement with a pound sign (#). These temporary tables are Adaptive Server Anywhere declared temporary tables, and are available only in the current connection. For information about declared temporary tables in Adaptive Server Anywhere, see DECLARE LOCAL TEMPORARY TABLE statement.

By default, columns in Adaptive Server Enterprise default to NOT NULL, whereas in Adaptive Server Anywhere the default setting is NULL, to allow NULL values. This setting can be controlled using the allow_nulls_by_default option. You should explicitly specify NULL or NOT NULL to make your data definition statements transferable.

For Info     For information on this option, see Setting options for Transact-SQL compatibility.

Physical placement of a table is carried out differently in Adaptive Server Enterprise and in Adaptive Server Anywhere. The ON segment-name clause is supported in Adaptive Server Anywhere, but segment-name refers to an Adaptive Server Anywhere dbspace.

For Info     For information about the CREATE TABLE statement, see CREATE TABLE statement.

Top of page  Writing compatible queries

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

This section is concerned with compatible SELECT statement syntax, and assumes compatible data types, expressions, and search conditions. The examples assume that the QUOTED_IDENTIFIER setting is OFF: the default Adaptive Server Enterprise setting, but not the default Adaptive Server Anywhere setting.

The following subset of the Transact-SQL SELECT statement is supported in Adaptive Server Anywhere.


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 [ ASC | DESC ], ... ] |
| [ ORDER BY integer [ ASC | DESC ], ... ] |


{ table-name | alias-name = expression }...

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

identifier | 'string' | "string"

For Info     For a full description of the SELECT statement, see SELECT statement.

The following keywords and clauses of the Transact-SQL SELECT statement syntax are not supported by Adaptive Server Anywhere:


Top of page  Compatibility of joins

In Transact-SQL, joins are specified in the WHERE clause, using the following syntax:

Start of select, update, insert, delete, or subquery
FROM {table-list | view-list} WHERE [ NOT ]
...[ table-name.| view name.]column-name
...[ table-name.| view-name.]column_name
...[ { AND | OR } [ NOT ]
... [ table-name.| view-name.]column_name
[ table-name.| view-name.]column-name
end of select, update, insert, delete, or subquery

The join_operator in the WHERE clause may be any of the comparison operators, or may be either of the following outer-join operators:

The Transact-SQL outer-join operators are supported in Adaptive Server Anywhere as an alternative to the native SQL/92 syntax. You cannot mix dialects within a query. This rule applies also to views used by a query(an outer-join query on a view must follow the dialect used by the view-defining query.

Adaptive Server Anywhere also provides a SQL/92 syntax for joins other than outer joins, in which the joins are placed in the FROM clause rather than the WHERE clause.

For Info     For information about joins in Adaptive Server Anywhere and in SQL/92, see FROM clause.

For Info     For more information on Transact-SQL compatibility of joins, see Transact-SQL outer joins.

Top of page  

Collection Contents Index Configuring databases for Transact-SQL compatibility Transact-SQL procedure language overview pdf/chap30.pdf