Sybase ASE Versions Evolution

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:

Adaptive Server authenticates clients with data from an LDAP server. Users authenticate with passwords stored on an LDAP server rather than in the syslogins catalog. The LDAP server provides a centralized location for login accounts-both names and passwords.

Adaptive Server servers share user login data stored on the LDAP server. Information formerly stored in syslogins is now managed and stored on an LDAP server. It is cached locally to preserve referential integrity and for other, database-specific uses.

With LDAP enabled, users have a single login and password throughout the enterprise.

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:

default in the insert statement;

set for variable and value assignments;

square brackets surrounding identifier names;

new built-in function cast() and those added in version 12.5.0.3: len(), left(), day(), month(), year(), str_replace(), newid(), square().

derived tables, which can be used wherever a view can be used, and which can be used in the FROM LIST of SELECT, SELECT INTO, CREATE VIEW, and INSERT statements.

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.

year;

month;

day;

str_replace;

square;

left;

len;

next_identity;

identity_burn_max;

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:

Replicates both tables and stored procedures;

Maintains database integrity and transactional consistency at multiple sites;

Provides guaranteed delivery of replicated data;

Manages database objects with a publish-and-subscribe model;

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:

Iterating through XQL result sets to extract XML fragments;

Converting XML into SQL data, for insertion into database tables;

Getting the results as a DOM (Document Object Model) object;

Dynamic memory allocation. Allows allocating total physical memory dynamically. Many of the configuration parameters that affect memory are now dynamic, and the server does not have to be restarted for them to take effect.

For Adaptive Server 12.5, both the data cache and the procedure cache are specified as absolute values. The sizes of the caches do not change until DBA reconfigure them. In earlier versions of Adaptive Server, the size of the procedure cache was based on a percentage of the available memory. After DBA configured the data cache, whatever was left over was allocated to the procedure cache.

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:

Allows Java methods to return output parameters and result sets to the calling environment.

Allows developers to take advantage of traditional SQL syntax, metadata, and permission capabilities.

Complies with Part 1 of the ANSI SQLJ standard specification.

Allows developers to use existing Java methods as SQLJ procedures and functions on the server, on the client, and on any SQLJ-compliant, thirdparty database.

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.

Select raw data from Adaptive Server using XQL and display it as an XML document. User can also store XML documents in Adaptive Server.

Use the Java XML parser provided by Sybase, which allows you to install his query engine as either a standalone program or inside Adaptive Server.

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. Java.net support. With java.net, 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:

Download documents from any URL address on the Internet.

Send e-mail messages from inside the server.

Access documents using XML.

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.

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.

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.

Java programming language can be used as an integral part of Transact-SQL.

Java code can be reused in different layers of an application - client, middle-tier, server - and can be consequently used wherever it makes most sense.

Java provides a more powerful language than stored procedures for building logic in the database.

Java classes become rich, user-defined datatypes.

Methods of Java classes provide new functions accessible from SQL.

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:

Transitive closure can be applied to joins.

Predicate factoring and transformation can improve costing for queries using or.

Special like optimization for leading wildcards in strings.

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.

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

Bring Adaptive Server into full compliance with the X/Open XA protocol when acting as a resource manager, without requiring additional services as XA-Server.

Provided support for distributed transactions coordinated by Microsoft Distributed Transaction Coordinator (MSDTC).

Ensure consistent commit or rollback for all transactions that update Adaptive Server data via remote procedure calls (RPCs) and Component Integration Services.

Provide the framework to support additional distributed transaction management protocols in the future.

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

Adaptive Server includes support for the European currency symbol, or "Euro".

Adaptive Server can now perform character set conversions that cause a change in the data length;

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

Datapages locking, which locks only the data pages;

Datarows locking, which locks only the data rows, also known as row-level locking;

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.

Select, delete, update, readtext, and writetext commands now include the readpast option. This option allows the command to skip all pages or rows that are not immediately accessible due to incompatible locks.

A new command, lock table, explicitly locks entire tables in either shared or exclusive mode.

Server-level and session level options that specify how long tasks wait for locks.

Support for repeatable reads transaction isolation level (level 2) for data-only-locked tables.

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.

User indexes.

Objects such as procedures, triggers, views, rules, and defaults.

Databases.

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.

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.

System Administrators can now configure maximum numbers of rows stored on a data page or index leaf page. This reduces lock contention and improves concurrency for frequently accessed tables.

In Release 11.0, SQL Server provides the ability to partition heap tables. A partition is simple another term for a page chain. Partitioning a table creates multiple page chains (partitions) for the table and, therefore, multiple last pages for insert operations.

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).

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;

a) Integrity constraints. In addition to the rules, triggers, defaults, and unique indexes already provided by SQL Server by allowing specify integrity constraints in the create table statement. These include:

unique and primary key constraints to insure unique values in a column;

Referential integrity constraints to guarantee that a primary key exists in another foreign key table;

Check constraints to limit the values that can be inserted into a table;

Defaults to specify default values for a column;

b) Changes to Views.

The distinct keyword can now be used in view definition, allowing creating views that do not contain duplicate rows.

The with check option clause has been added to the create view statement. When a view is created with check option, all rows inserted or updated through the view must meet the view criteria.

Create view statements can been included in batches with other transact-SQL statements.

c) The new create schema command allows creating several objects and grant permissions on those objects in a single statement, which can be committed or rolled back as a unit.

d) Release 10.0 of SQL Server provides these new datatypes:

Numeric or decimal (dec) specify exact numeric values, with specified precision and scale to indicate the number of digits and number of digits to the right of the decimal point.

Double precision specifies an approximate numeric type.

The following changes have been made to existing datatypes:

Float now accepts an optional precision specification.

The default length for char, varchar, nchar, and nvarchar columns is 1 in create table statements, variable declarations, and parameter specifications.

e) Automatic sequential values using the IDENTITY property for a column. Each table in a database can have a single IDENTITY column. This column is used to store numbers that are automatically generated by SQL Server. The value of the IDENTITY column uniquely identifies each row in a table.

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.

FIPS Flagger. For customers writing application that must conform to the ANSI standard, SQL Server provides a set fipsflagger option to flag incompatible syntax.

SQLSTATE messages and codes. By default, SQL Server returns SQLSTATE values to embedded SQL applications, as required by entry level SQL92.

The escape clause in the like predicate. SQL Server now supports the ANSI escape clause, which allows specifying an escape character in the like predicate to search for wildcard characters. This is an addition to Transact-SQL's use of square brackets for the same purpose.

ANSI-style comments. In Transact-SQL, comments are delimited by /* */ pairs, and can be nested. Transact-SQL now also supports ANSI-style comments, which consists of any string beginning with two unspaced minus signs, a comment, and a terminating newline. This syntax conflicts with subtraction of a negative number.

SQL Server now provides ANSI-compliant "chained" transaction behaviors as option. In chained mode, all data retrieval and modification commands (delete, insert, open, fetch, select, and update) implicitly begin a transaction.

Delimited identifiers. SQL Server now supports delimited identifiers for table, view, and column names. Delimited identifiers are object names enclosed within double quotation marks. Using them allow avoiding certain restrictions on object names.

Treatment of nulls. A new set option, ansinull, determines whether or not evaluation of NULL-valued operands in SQL equality (=) or inequality (!=) comparisons and in aggregate functions is ANSI-compliant.

sp_syntax displays the syntax of Transact-SQL statements, system procedures, utilities, and DB-Library routines.

New system procedures allowing users easy access to information about SQL Server. These stored procedures are compatible with the catalog interface for the Open database connectivity (ODBC) API. The procedures are: sp_column_privileges, sp_columns, sp_databases, sp_datatype_info, sp_fkeys, sp_pkeys, sp_server_info, sp_proc_columns, sp_statistics, sp_stored_procedures, sp_table_privileges, sp_tables.

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.

SQL Server support conversion among a variety character sets.

The System Administrator can now change the default, installed sort order, or character set used by SQL Server.

New datatypes. The national character datatypes nchar and nvarchar allow a user to define a column length as containing a specific number of multibyte characters.

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.

Ispirer SQLWays Database Migration Software

Sybase ASE Versions Evolution