Contents Index Transact-SQL Compatibility Adaptive Server architectures

ASA SQL User's Guide
  Transact-SQL Compatibility

An overview of Transact-SQL support


Adaptive Server Anywhere supports a large subset of Transact-SQL, which is the dialect of SQL supported by Sybase Adaptive Server Enterprise. This chapter describes compatibility of SQL between Adaptive Server Anywhere and Adaptive Server Enterprise.

Goals 

The goals of Transact-SQL support in Adaptive Server Anywhere are as follows:

The aim is to write applications to work with both Adaptive Server Enterprise and Adaptive Server Anywhere. Existing Adaptive Server Enterprise applications generally require some changes to run on an Adaptive Server Anywhere database.

How Transact-SQL is supported 

Transact-SQL support in Adaptive Server Anywhere takes the following form:

The following diagram illustrates how the two dialects overlap.

A Venn diagram of two circles. The non-overlapping parts of the two circles show statements such as CREATE PROCEDURE and CREATE TRIGGER that have distinct syntaxes in Adaptive Server Anywhere and Transact-SQL. The overlapping area shows statements that are the same in both servers, such as SELECT, INSERT, UPDATE and DELETE.
Similarities and differences 

Adaptive Server Anywhere supports a very high percentage of Transact-SQL language elements, functions, and statements for working with existing data. For example, Adaptive Server Anywhere supports all of the numeric functions, all but one of the string functions, all aggregate functions, and all date and time functions. As another example, Adaptive Server Anywhere supports Transact-SQL outer joins (using =* and *= operators) and extended DELETE and UPDATE statements using joins.

Further, Adaptive Server Anywhere supports a very high percentage of the Transact-SQL stored procedure language (CREATE PROCEDURE and CREATE TRIGGER syntax, control statements, and so on) and many, but not all, aspects of Transact-SQL data definition language statements.

There are design differences in the architectural and configuration facilities supported by each product. Device management, user management, and maintenance tasks such as backups tend to be system-specific. Even here, Adaptive Server Anywhere provides Transact-SQL system tables as views, where the tables that are not meaningful in Adaptive Server Anywhere have no rows. Also, Adaptive Server Anywhere provides a set of system procedures for some of the more common administrative tasks.

This chapter looks first at some system-level issues where differences are most noticeable, before discussing data manipulation and data definition language aspects of the dialects where compatibility is high.

Transact-SQL only 

Some SQL statements supported by Adaptive Server Anywhere are part of one dialect, but not the other. You cannot mix the two dialects within a procedure, trigger, or batch. For example, Adaptive Server Anywhere supports the following statements, but as part of the Transact-SQL dialect only:

Adaptive Server Anywhere only 

Adaptive Server Enterprise does not support the following statements:

Notes 

The two dialects cannot be mixed within a procedure, trigger, or batch. This means that:


Contents Index Transact-SQL Compatibility Adaptive Server architectures