Sybase ASA Versions Evolution

1. XML support. Adaptive Server Anywhere 9.0 supports XML, including storing XML document, exporting relational data as XML, importing XML, and returning XML from queries on relational data.

SQL/XML support. SQL/XML is a standard that describes using SQL in conjunction with XML. As part of its SQL/XML support, Adaptive Server Anywhere includes an XML data type that can be used to store XML documents in the database.

FOR XML clause. The SELECT statement support FOR XML clause that allows obtaining query results as an XML document.

2. HTTP server in the database. Adaptive Server Anywhere database servers can presently act as web servers, making possible the running of web-based applications using only an Adaptive Server Anywhere database and a web browser.

3. 64-bit version available. A full 64-bit version of the software is available for Windows Server 2003 on Itanium II. A deployment release is available on 64-bit Linux and HP-UX operating systems.

4. The WITH clause allows specifying common table expressions. Common table expressions are temporary view definitions existing only within the scope of a SELECT statement. They can be recursive, or non-recursive. They also permit to perform multiple level of aggregation within a single query.

5. Recursive union can now be performed using a common table expression of a particular form. Recursive common table expressions make possible the writing of recursive queries. These are particularly useful when querying tables representing hierarchical structures or directed graphs. Each recursive common table expression contains an initial sub-query, which is executed first, and a recursive sub-query. The reference to the view, which must appear within the FROM clause of the recursive sub-query, references the rows added to the view during the previous iteration.

6. INTERSECT and EXCEPT operations supported. These operations compute intersection and difference between two or more result sets. They complement the UNION operation.

7. SELECT statements can operate on stored procedure result sets. In SELECT statements, a stored procedure call can now appear at any place where a base table or view is allowed.

ROLLUP operation. For queries with a GROUP BY clause, the ROLLUP operation adds subtotal rows into the result set. Each subtotal row provides an aggregate over a set of rows in the GROUP BY clause.

The LIST function can include ordered lists.

Addition aggregate functions. Functions have been added to compute sample-based and population-based deviations and variances.

9. The CREATE INDEX statement permits an index to be created on a built-in function. This feature is a convenience method adding a new computed column to the table, and creating an index on that column.

10. ORDER BY clause allowed in all contexts. In the earlier releases, it was not allowed for many SELECT statements in view definitions, in sub-queries, or in UNION operations to use ORDER BY clause. This restriction has presently been removed.

11. SELECT statements can now include START AT as part of the TOP clause. START AT provides additional flexibility in queries that impose explicit limitations on the result set.

12. Constraints can now be named. Names can be now assigned to check constraints, unique constraints, and referential integrity constraints.

13. Lateral derived tables permit outer references in the FROM clause. Outer references can now be made from derived tables and from stored procedures in the FROM clause. The LATERAL keyword is used to indicate that an outer reference is being made.

14. CREATE FUNCTION and ALTER FUNCTION now permit Transact-SQL syntax. User defined functions can now be created in the Transact-SQL dialect that return a scalar value to the calling environment.

1. Clustered index support. Creating a clustered index on a table causes the rows in that table to be stored approximately in the same order as they appear in the index.

2. Unique identifier support. Adaptive Server Anywhere supports unique identifiers (UUIDs and GUIDs). UUIDs (universally unique identifiers) and GUIDs (global unique identifiers) that allows uniquely identifying rows, even across distinct databases.

3. Update existing rows with the ON EXISTING clause. The ON EXISTING clause of the INSERT statement can be used to update existing rows with new values, as long as the table has a primary key.

4. New joins added. New joins added to this release include the nested loop semijoin, the nested loop anti-semijoin, the hash semijoin and the hash anti-semijoin.

5. Hide procedure text to keep logic confidential. The logic contained in stored procedures, functions, triggers, and views can be hidden by using the SET HIDDEN option. This allows applications and databases to be distributed without revealing the logic in stored procedures, functions, triggers, and views.

7. INSERT statement presently supports WITH AUTO NAME. If WITH AUTO NAME is specified in an INSERT statement, the names of the items in the SELECT list determine the associations of values to destination columns. This is useful when the number of columns in the destination table is too large.

8. Triggers can discriminate among the actions that caused a trigger to fire. Different actions now carried out depending on whether the trigger was fired by an INSERT, UPDATE, or DELETE operation. This feature enables sharing logic among the different events within a single trigger, and carrying out some actions in an action-depend manner.

1. Determine ANSI equivalency of non-ANSI statements. The REWRITE functions accept a new parameter, ANSI, which cause the function to return the ANSI equivalent of any SELECT, DELETE, or UPDATE statement.

2. Variable assignment allowed in the UPDATE statement. The SET clause of the UPDATE statement can now be used for assigning a value to a variable, in addition to updating the table. This feature is compatible with Adaptive Server Enterprise.

4. Specify isolation level in the FROM clause. The WITH table-hint argument can be used for specifying a locking method for a particular table or view for particular SELECT, UPDATE, or DELETE statement.

1. Database properties for padding and case sensitivity. Two new properties to determine if database uses blank padding when comparing strings (BlankPadding) or if database is case sensitive (CaseSensitive).

1. Oracle, IBM DB2, Microsoft SQL Server, Sybase Adaptive Server Enterprise, Sybase Adaptive Server Anywhere, and Microsoft Access databases can be migrated (imported) remotely into Adaptive Server Anywhere using the new sa_migrate set of stored procedures.

2. Truncate timestamp option for compatibility with non-Adaptive Server Anywhere databases, timestamp values can be truncated.

1. Unloading result sets. The new UNLOAD SQL statement makes possible the unloading of query result set into a comma-delimited text file.

2. Distributed transactions and three-tired computing. Distributed transactions include operations on more than one server in a single transaction. A transaction server controls the commit and rollback behavior of distributed transactions.

In this release, Adaptive Server Anywhere can participate in distributed transactions coordinated by the Microsoft Distributed Transaction Coordinator (DTC). Products such as Sybase Application Server and Microsoft Transaction Server can use DTC for the coordination of transaction, so DTC support enables Adaptive Server Anywhere to participate in three-tired computing with these products.

1. Read-only databases. Database can be designated as read-only when a database server is started. This feature makes deployment of databases on read-only media, such as CD-ROMs, more straightforward.

1. The ODBC driver has been updated to ODBC 3.51. This version of ODBC includes support for Unicode Applications.

1. Java in the database. Adaptive Server Anywhere provides a runtime environment for Java in the database, and allows using Java classes as SQL data types.

3. SQL Anywhere Version 5 and other previous releases of the software have been able to handle multiple connections concurrently, but query processing has been carried out on only a single operation system thread. Consequently, even on multi-processor machines, only a single processor was used for query processing.

4. The data processing engine in Adaptive Server Anywhere can now run using multiple operation-system threads, and hence can take advantage of multiple processors. By default, Adaptive Server Anywhere uses the same number of operating system threads as the machine has CPU's.

5. Multi-processor support in Adaptive Server Anywhere enables transactions from separate connections to run simultaneously on separate CPU's. A single connection uses a single thread, and no single requests are split among CPU's.

Rewrite optimization to transform a query into a query that has a greater potential for cost-based optimization to find the most efficient access plan.

Carries out the following kinds of transformation for rewriting optimization:

Subquery rewriting. In many cases subquery rewritten as joins.

Elimination of unnecessary DISTICT clauses.

Subquery caching. There are subqueries that cannot be rewritten as joins - for example, subqueries involving aggregate functions. In these cases, there is a trade off between evaluating the subquery each time it is referenced (CPU time) or caching (storing) the subquery result for repeated use (memory use, and possible disk access).

Adaptive Server Anywhere Version 6 carries out some caching of subqueries.

Fractional user-supplied estimates. In those cases where the cost-based optimizer does not generate the best access plan, and where query performance is important, users can supply estimates of various parts of the query in order to prompt the optimizer.

New ODBC features:

Native ODBC. Presently the ODBC driver sends commands directly to the database server, without using "native" library. An ODBC driver in previous versions translated ODBC calls to a set of "native" calls, which were passed to the native interface DLL.

Adaptive Server Anywhere supports ODBC 3.0.

UNSIGNED data types. Unsigned versions of the INT and SMALLINT data types have been added. These versions can hold only non-negative numbers, but have a large maximum value than signed version.

New data types. A 64-bit integer data type (BIGINT) has been added, in both signed and unsigned versions. Also, a VARBINARY data type is supported.

SELECT list enhancements. The first few rows of a query can be retrieved using the following syntax: SELECT [FIRST|TOP n] select_list.

Derived tables. Now, queries can be used in FROM clause. This makes possible writing complex queries without creating a view.

Computed columns. The value of a computed column is an expression, usually based on other columns.

1. A new special constant, LAST USER, has been added for identifying which user was the last to change a row.

2. Index names must now be unique for a given table, instead for a given user ID.

3. EXECUTE (string) is an alternative syntax for the EXECUTE IMMEDIATE statement.

4. A new database option, FIRE_TRIGGERS, allows trigger firing to be turned off. This is relevant for SQL Server to SQL Anywhere replication.

With release 5.5, Sybase SQL Anywhere can be obtained in a Standard and a Professional Edition. The Professional Edition includes additional components. In particular, the following components are included in the Professional Edition:

NetImpact Dynamo Internet features;

The Powersoft Infomaker reporting tool;

1. Several database options have been added to improve compatibility with SQL Server and ISO/SQL92, while maintaining the ability to be compatible with the existing applications.

2. The CONVERT function presently converts strings to dates and times, as well as converse.

3. Live backup. A new option for the backup utility allows a continuous backup of the transaction log. In case of a server shutdown, this log file can be used for a rapid restart of the system.

1. A new clause has been added to the DESCRIBE statement to retrieve column names with more than thirty characters.

2. ALTER VIEW and ALTER TRIGGER statements make possible the replacement of an existing view or trigger definition by a new definition.

3. Transact-SQL accepted by parser. To enhance the SQL Server compatibility, performance parameters that can be used in SELECT, UPDATE, and DELETE statements in System 11 are now accepted and discarded by SQL Anywhere. Also, the PREFETCH System 11 option is allowed, but has no effect.

1. CREATE SCHEMA statement creates a collection of tables, views, and associated permissions for a user.

4. Column default extension. Constant expressions are now allowed as column defaults, as long as they do not reference database objects.

CREATE TABLE and CREATE INDEX statements now support ON segment-name clause. CREATE INDEX permits CLUSTERED and UNCLUSTERED keywords, although no clustering is performed.

The system objects owned by the DBO user ID are now not unloaded by either Unload or Extraction utilities.

6. Multiple-event triggers. A single trigger can presently be defined to handle a combination of INSERT, UPDATE, and DELETE operations on a table.

1. Numbering scheme. Sybase SQL Anywhere uses numbers for indicating patch or maintenance-level release. Release 5.0.01 is the first maintenance-level release for Sybase SQL Anywhere 5.0. The numbering scheme replaces alphabetic scheme used for Watcom SQL patch-level releases.

2. Release 5.0.01 is released to address some problems in the initial 5.0 release, particularly a backwards-compatibility problem.

With the Sybase\Powersoft merger of February 1995, Watcom became a part of Sybase, Inc. Watcom SQL is presently part of the Sybase System 11 product line, and this change is reflected in the new name of the product, SQL Anywhere. The principal dialect of SQL supported by SQL Anywhere is named Watcom-SQL.

1. SQL Central database management tool. SQL Central is a graphical database administration tool that runs on the Windows 95 and Windows NT 3.51 operation systems.

2. Transact-SQL compatibility. SQL Anywhere 5.0 includes a set of extensions to Watcom-SQL from the Sybase Transact-SQL dialect. This makes the development of compatible applications for SQL Anywhere and SQL Server database servers much more straightforward, and also brings new features to all SQL Anywhere users.

3. ODBC 2.5 support. SQL Anywhere 5.0 now supports ODBC 2.5 at level 2 for the Windows 95 and Windows NT operating systems, and ODBC 2.1 for the Windows 3.x operating systems.

4. Open Server Gateway. The Open Server Gateway allows client applications to work with both with SQL Server and SQL Anywhere database servers.

5. Transaction log mirroring. SQL Anywhere can optionally maintain two identical transaction logs. Transaction log mirroring provides additional protection against loss of data due to disk failure.

6. Calls to external DLLs from procedures. You can now create procedures that call external DLLs.

7. Stored procedure extensions. Stored procedures have more flexible parameter declarations (including default values and optional parameters in CALL statement), and can return status information.

8. User-defined functions. The new CREATE FUNCTION statement of the SQL Anywhere stored procedure language can be used to define new functions, which can be used just like other functions.

9. Batches. This release introduces support for batches of SQL statements. Control statements (IF, LOOP, and so on) are presently available in command file as well as in procedures and triggers.

10. Statement level triggers. Triggers can be made to fire once after each statement, as alternative to the row-level triggers supported in the earlier versions.

12. New built-in functions. New numeric, string, date and time, and data type conversion functions have been added, as well as new system functions. New functions include:

System functions describing database properties;

PATINDEX for pattern matching;

13. User-defined data types. User can define own data types from the existing base data types supported by SQL Anywhere.

14. Extensions to data manipulation statements. The DELETE and UPDATE statements have been extended to support criteria based on joins.

15. System stored procedures. SQL Server provides stored procedures for carrying out database management functions. The system tables of SQL Server and SQL Anywhere are different, but several of system procedures are provided to carry out analogous actions on each.

16. More flexible column constraints. Prior to this release, all constraints associated with a table, whether column constraints or table constraints, were held as a single table constraint. Column constraints are now held individually, which allows them to be individually deleted or replaced.

17. Other new statements have been added: LOAD TABLE, UNLOAD TABLE, TRUNCATE TABLE, MESSAGE, EXECUTE IMMEDIATE, RETURN, and others.

Ispirer SQLWays Database Migration Software

Sybase ASA Versions Evolution