Table of ContentsPreviousNext

Ispirer             Ispirer

Ispirer SQLWays Database Migration Software

Sybase ASE Versions Evolution

Version 12.5.1

1. Dynamically altering the data cache without having to restart the server

2. Automatic expansion of databases and devices. Databases can be configured to expand automatically when they run out of space.

3. Supports the UTF-8 sort order. In earlier versions, when using UTF-8, the two sort order choices were binary and no-case (ASCII-only). Version 12.5.1 provides the ability to perform non-binary sort orders in UTF-8. As such, all sort orders available for the unichar and univarchar data types can be used for char or varchar data when the default character set is set to UTF-8.

4. Allows using unichar and univarchar datatypes with any server's default character set without having first to configure your default character set to UTF-8.

5. SQL derived tables. A SQL derived table is created with a nested select statement, as in the following example: select * from (select * from t) dt.

6. Date and time have been added as separate data types. In earlier versions, only datetime and smalldatetime were available.

7. Added XML mapping. The for xml clause in select statements and the forxmlj function map SQL result sets to SQL-XML document, using the SQLX-XML format defined by the ANSI SQLX standard.

8. Uses the native XML processor supporting SQL extensions that perform XML query functions on XML documents. Integration of the native XML processor in Adaptive Server provides remarkable performance improvements over the Java-based XQL processor of earlier versions of Adaptive Server. The native XML processor supports standard XML documents and standard XPath queries, which are a subset of the new XQuery language.

9. Adaptive Server extends its LDAP support to include storage of user information. With LDAP services enabled:

10. Adaptive Server Enterprise Web Services enable Adaptive Server Enterprise to both provide and use Web services. Client applications can access SQL and stored procedures in Adaptive Server using SOAP.

11. Migrate more easily between Adaptive Server and Microsoft-SQL Server. Adaptive Server 12.5.1 provides enhanced compatibility with Microsoft SQL extensions:


1. Asynchronous log service (ALS) and optimistic index locking help resolve drastically increased contention on such key resources as the log, the spinlocks that guard the log, and the address locks, increasing Adaptive Server scalability in systems using four or more online engines.

2. Allows creating and managing multiple temporary databases in addition to the system tempdb, which was the only temporary database in the server in earlier versions of Adaptive Server.

3. Improved performance for select into.

4. New functions:


1. Adaptive Server is available in the Enterprise Edition, the Small Business Edition, and the Developer's Edition. The Enterprise Edition of Adaptive Server is a full-featured server that can run all optional features. The Small Business Edition of Adaptive Server includes the features required by most small businesses, but excludes some of the more advanced features. The Developer's Edition is designed for you to design and build applications for Adaptive Server in a development environment. Earlier versions of Adaptive Server were sold either at the Enterprise Level or the Workplace Level.

2. ASE Replicator extends Adaptive Server Enterprise capabilities and provides basic replication from a primary database to one or more replicate databases. ASE Replicator:

3. The Extensible Query Language (XQL) result set feature allows accessing query results as objects, rather than as strings, and extracting SQL base types easily from an XML document, without parsing results or writing code. The XQL engine is written in Java, so creating a Java object is easy. The engine allows:

Version 12.5

1. Dynamic reconfiguration.

2. Extends Java capabilities. Developers can now wrap Java static methods in SQL names and create SQLJ stored procedures or functions that developers can use as would Transact-SQL stored procedures or built-in functions. This new functionality:

3. XML in the database. XML allows defining own application-oriented markup tags. This feature, written entirely in Java, also includes methods for storing XML documents and generating them from SQL data.

User can:

4. Supporting union operators in select statements that define views.

5. Using Internet directory services (LDAP).

6. Implementing Secure Sockets Layer (SSL) protocol.

7. Provides the Enterprise Java Beans (EJB Server), a component transaction server. EJB Server provides the framework for creating, deploying and managing middle-tier business logic in the form of EJBs in a multitier environment.

8. External file system support. Enables SQL access to file system directories and files. The supported syntax is: create [existing] table fname external file at "directory_pathname" column delimiter "delimiter".

9. Row-level access control. Row-level access control enables the Database owner or table owner to control the rows in a table that user can access, based on their identification or profile and the privileges the user has from the application level.

10. support. With, developers can create client-side Java networking applications within the server. Developers can create a network Java client application that connects to any server, which enables Adaptive Server to function as a client to external servers. Developers can:

11. unichar support. Adds two new datatypes using UTF-16 encoding of the Unicode char. The unichar and univarchar datatypes are independent of the existing char and varchar datatypes, but have the same functionality. Unichar is a fixed-width, non-nullable data type (like char) and univarchar is a variable-width, nullable data type (like varchar). The set of the built-in string functions that operate on char and varchar, also operate on unichar and univarchar. Unlike the existing char and varchar, the new unichar and univarchar only store UTF-16 characters and have no relation to the default character set or default sort order of Adaptive Server. To use these news datatypes, the default character set for the server must be set to UTF-8.

Version 12.0

Availability and manageability:

1. Sybase Failover for a high availability system. Sybase Failover enables Adaptive Server to work in a high availability cluster in an active-active configuration. Both nodes in the cluster include Adaptive Servers managing independent workloads and capable of taking over each other's workload in the event of a failure. The Adaptive Server that takes over the workload is called a secondary companion, and the Adaptive Server that fails is called the primary companion. Together they are companion servers. This movement from one node to another is called failover. After the primary companion is ready to resume its workload, it is moved back to its original node. This movement is called failback. Clients connected to the failed Adaptive Server automatically reestablish their connection via the second machine.

2. Supporting UNIX files system. Introduces a new dsync setting for database device files, which controls whether or not data written to those files are buffered. When the dsynch setting is on, Adaptive Server opens a database device file using the UNIX dsync flag. The dsync flag ensures that data is written to the device file directly on the physical storage media, and Adaptive Server can recover data on the device in the event of a system failure.

3. Modifying an existing table's schema with alter table. Includes new syntax for alter table that allows users to add, drop, or modify a table's columns. Alter table allows users to modify a column's data type, length, locking scheme, or default value. Alter table also allows users to add, drop, or modify an IDENTITY column and to add a null or non-null column.

4. Suspending database updates with quiesce database command. User may want to suspend database updates to use external database backup utility, or to separate a database from its mirror for reporting purposes.

Performance and productivity:

1. Provides a runtime environment for Java for executing Java code in the server. Adaptive Server's Java capabilities provide users with powerful ways of managing and storing both data and logic, using a language that is both portable and widely available.

2. Merge joins as a query execution method for equijoins. Previous versions of Adaptive Server performed all join queries as nested-loop joins. Merge joins can produce dramatic improvements in query execution time, especially for joins involving several very large tables.

3. Query costing improvements. The following changes help improve the accuracy of optimizer costing and provide additional paths for query execution:

4. ANSI syntax for joining tables or views. Previous versions of Adaptive Server provided syntax only for a transact-SQL join, which include *= and =* symbols for specifying a right or a left join, respectively.

5. Dynamic execution of Transact-SQL. Adaptive Server version 12.0 provides an extension to the execute command that enables defining transact-SQL commands dynamically at execution time. Applications and procedures can use the new execute() syntax in cases where table and column names are not known until the application or procedure executes.

6. Text and image data type enhancements. Adaptive Server version 12.0 improves the storage format for text and image datatypes. Changes to the storage format are transparent to end-user applications, but they enable Adaptive Server to perform random access when querying data. This improves query performance over early versions, which had to access text and image data sequentially. The new storage format for text and image datatypes also enables Adaptive Server to asynchronously prefetch the data into an existing buffer pool.

7. Adaptive Server can capture query text and save an abstract plan for a query in a new system table called sysqueryplans. Using a rapid hashing method, incoming SQL queries can be compared to stored query text, and if a match is found, the saved abstract plan is used to execute the query.

8. Adaptive Server version 12.0 introduces disable trigger option of the alter table command to disable any triggers in a database before load the database, reducing the time required to load the database.

9. Adaptive Server version 12.0 allows dividing each cache into partitions, each with its own spinlock. In the previous versions, each task that needs to access the data cache holds a spinlock on the cache while accessing it. With a large number of engines and a high transaction rate, contention for buffer cache spinlocks can slow performance.

Distributed processing:

1. Adaptive Server version 12.0 introduces several Distributed Transaction Management features, which:

Version 11.9.2

1. Direct Updates Through Joins. In version 11.9.2, many restrictions on when direct updates can be performed have been removed.

2. Character Set Changes:

Version 11.9

1. New Locking Schemes. The Adaptive Server version 11.9 provide two new locking schemes to improve the concurrence and performance of Adaptive Server:

The datapages and datarows locking schemes share many characteristics, including the fact that they do not acquire locks on index pages, so together they are often referred to as the data-only locking schemes.

The pre-11.9 locking scheme continues to be support; it is called allpages locking. Allpages locking locks the data pages and index pages affected by query.

When a query updates a value in a row in an allpages-locked table, the data page is locked with an exclusive lock. Any index pages affected by the update are also locked with exclusive locks. These locks are transactional, meaning they are held until the end of the transaction.

In datapages locking, entire data pages are still locked, but index pages are not locked. When a row needs to be changed on a data page, that page is locked, and the lock is held until the end of the transaction.

In datarows locking, row-level locks are acquired on individual rows on data pages. Index rows and pages are not locked. When a row needs to be changed on a data page, a nontransactional latch is acquired on the page. The latch is held while the physical change is made to the data page, and then the latch is released. The lock on the data row is held until the end of the transaction. The index rows are updated using latches on the page, but are not locked. Index entries are implicitly locked by acquiring a lock on a data row.

2. The create index command allows you to specify ascending or descending order for each column in the index. In earlier versions, all indexes were created in ascending order for all columns. Scans that needed to read the data in reverse order could scan the index backward, but if the required order was a mix of ascending and descending order on the keys, the query needed to perform a sort step.

3. Transact-SQL syntax provides new commands and options that affect locking:

Version 11.5

1. Asynchronous prefetch improves performance by anticipating the pages that will be required for certain well-defined classes of database activities whose access patterns are predictable. The I/O requests for these pages are issued before the query needs them so that most pages are in cache by the time query processing needs to access the page.

2. Simplifies standard SQL expressions by allowing a case expression instead of an if...else construct.

3. OmniConnect has been replaced by Component Integration Services (CIS). The Component Integration Services (CIS) feature allows connecting to remote Sybase and non-Sybase databases. It presents a uniform view of enterprise data to client applications and provides location transparency to the data sources that clients access.

4. Create index enhancements. Create clustered index...with sorted_data does not sort or copy data except when certain optional clauses are specified to create index. If these clauses are specified for partitioned tables, a parallel sort must be performed. In earlier releases of SQL Server, use of the create clustered index...with sorted_data allowed to skip the sort step on data that was already in sorted order, however, the data pages always copied to a new location on the data devices.

5. Descending index scan optimization. Descending index scan optimization is a performance enhancement that can improve the performance of queries that use the desc keyword in order by queries to return result sets in descending order. In earlier releases of SQL Server, descending result sets required a worktable and a sort. In Adaptive Server 11.5, the optimizer can choose to use the index and avoid the sort step, if the strategy reduces the query cost.

Descending index scan can speed the use of both clustered and non-clustered index access, reduce the tempdb space required for temporary tables, save the CPU time required for sorts, and shorten the time that locks are held, if descending scans use holdlock or transaction isolation level 3.However, there can be increased chance of deadlocking in some applications.

This feature does not change the syntax for the order by clause, it only changes the way that order by clauses with the desc keyword can be optimized.

6. Directory services. Adaptive Server 11.5 provides an alternative to using the traditional interfaces file by supporting the ability to connect to network service information throw third-party directory service provider.

7. Extended Stored Procedures (ESPs). This release support both user-defined and system-defined extended stored procedures.

ESPs provide a method for calling procedural language function from within the Adaptive Server. The procedural language in which the functions are written must be capable of calling C language functions and manipulating C data types.

ESPs allow Adaptive Server to perform a task outside Adaptive Server in response to an event occurring within Adaptive Server. For example, an email notification could be sent in response to an event occurring within the RDBMS.

The interface to ESPs is similar to the interface to system procedures and user-defined stored procedures. The difference is that an ESP executes procedural language code, rather than Transact-SQL statements.

Extended stored procedures are implemented by an Open Server application called XP Server, which runs on the same machine as Adaptive Server. Running ESPs in a separate process protects Adaptive Server from failing because of faulty ESP code. Adaptive Server and XP Server communicate through the remote procedure calls (RPCs).

The function that implements the ESP is compiled and linked into a dynamic linked library (DLL) or shared library. Adaptive server looks in the system tables for the function that has the same name as requested ESP and passes the function name and the DLL name to XP server.

8. Adaptive Server allows managing individual metadata caches for:

Managing individual metadata caches for these objects is a beneficial for a server that contains a large number of user indexes and objects and where there is high concurrency among users.

9. Parallel bulk copy. Parallel bulk copy allows coping data in parallel to Adaptive Server from files. Parallel bulk copy substantially increases performance during bcp sessions because large bulk copy jobs can be split in multiple sessions and run concurrently.

10. Parallel queries and enhanced partitioning. When Adaptive server is configured for parallel query processing, the optimizer evaluates each query to determinate whether it is eligible for parallel execution. If it is, the query is divided into components that process simultaneously. The results are combined and delivered to the client in a shorter period of time than it would take to process the query serially as a single component.

Fully enabled parallel query processing requires multiple processes, engines, and partitions, resulting in increased overhead for Adaptive Server, additional CPU requirements, and increased disk I/O.

Pont-in-time recovery. Point-in-time recovery allows recovering a database by rolling it forward to a particular time in its transaction log.

11. Recovery fault isolation. With recovery fault isolation, a System Administrator has the option of isolating the corrupt pages marked suspect by recovery while the rest of database is brought online and available to users.

Prior to this release, when recovery detected corruption in a database, it would make entire database inaccessible. The database remained unavailable to users until the suspect pages has been repaired or removed from database.

12. SQL Server Manager has been replaced by Sybase Central Sybase's common management interface and the Adaptive Server plug-in for Sybase Central.

The Adaptive Server plug-in for Sybase central allows managing adaptive Server installations using the Sybase central graphical management tool.

Version 11.0

1. User-defined caches. Release 11.0 allows System Administrators to split the SQL Server data cache into separate named data caches and to bind databases or database objects to those caches. Also, System Administrators can create pools within caches that perform large I/O to disk, improving performance for many queries.

2. Data storage changes.

When a transaction inserts data into a portioned table, SQL Server randomly assigns the transaction to one of the table's partitions. Concurrent inserts are less likely to block, since multiple last pages are available for inserts.

3. Transaction log changes. There is one user log cache for each configured user connection. SQL Server uses this user log caches to buffer the user transaction records, which reduces the contention at the end of the transaction log. When a user log cache becomes full or another event occurs (such as transaction completes), SQL server "flushes" all log records from the user log cache to the database transaction log.

4. Isolation level 0. SQL Server allows specify isolation level 0 for the queries in transactions along with isolation levels 1 and 3 supported in release 10.0. Isolation level 0 prevents other transactions from changing data that has already been modified by uncommitted transaction. There other transactions are blocked from modifying data until the transaction commits. However, other transactions can still read the uncommitted data (known as dirty reads).

Version 10.0

1. SQL Server release 10.0 provides full support for cursors by implementing the new SQL commands: declare cursor, fetch, open, close, deallocate; keywords in the delete and update statements that allow updating and deleting at cursor positions;

2. Data Definitions Enhancements.

3. ANSI compatibility. SQL Server release 10.0 meets SQL89/FIPS 127-1 and SQL92 / FIPS 127-2 standards. In addition to major features such as declarative integrity constraints and cursors, which described earlier, the following changes are included in release 10.0.

Version 4.9.1

1. New system procedures.

Version 4.9

1. Multibyte character set feature. SQL Server now supports multibyte character sets, including EUC-JIS, SHIFT-JIS, and DEC-Kanji, for use in Asian installations. The following changes and features where introduced to provide flexible language support.

Version 4.8

1. Transact-SQL now provides the union operator. It allows combining the results of two or more queries into a single result set.

2. New "Not equals" operator. The new negative comparison operator <> (not equal to) is the same as the != operator.

3. New datatypes. Smallmoney, smalldatetime, and real are smaller versions of money, datetime, and float, respectively. They require 4 bytes of storage, rather than the 8 bytes required by their larger counterparts.

4. Quoted strings in column headings. Except in create table, create view, and select into statements, column headings now can include any characters, including blanks and SQL Server keywords, if the column heading is enclosed in quotes.

5. Symmetric multiprocessors features. Release 4.8 of the SQL Server explicitly designed to take full advantage of the capabilities of symmetric multiprocessor (SMP) systems.

Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.