New Features and Upgrading Guide
   CHAPTER 3. New Features in Adaptive Server Anywhere 6.0     

New SQL features

This section describes those new features that focus on enhancing and extending the SQL language supported by Adaptive Server Anywhere. It is not an exhaustive description of the differences in SQL between Version 5 and Version 6.

This section does not include changes described in other major areas described in this chapter, such as Java in the database and database administration statements.

• 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 larger maximum value than the signed version.

       For information, see Numeric data types.

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

       For information, see Numeric data types, and Binary data types.

• Additional functions     New functions have been added. Some of these provide improved Adaptive Server Enterprise compatibility.

  • Additional datepart support is provided in date functions.

         For information, see Date and time functions.

  • The STUFF and STR string functions have been added.

         For information, see String functions.

  • The CHAR_LENGTH function returns the number of characters in a string expression.

         For information, see String functions.

• SELECT list enhancements     You can retrieve the first few rows of a query using the following syntax:

  SELECT [ FIRST | TOP n ] select-list

  These are useful for queries using an ORDER BY clause, when only the first few of a potentially large result set are required.

       For information, see SELECT statement.

• Derived tables     You can use queries in the FROM clause. This allows groups of groups, or joins with groups, to be performed without creating a view.

       For information, see FROM clause.

• Computed columns     The value of a computed column is an expression, usually based on other columns. This feature is added partially to make it easier for existing databases to be enhanced using Java data types.

  You can add a column to a table using the following syntax:

  ALTER TABLE table-name
  ADD computed-column
    COMPUTE ( expression )

  For example:

  ALTER TABLE product
  ADD Inventory_Value 
    COMPUTE ( quantity * unit_price )

  Computed columns are read-only. They are ignored on INSERT and on UPDATE. Any UPDATE statement that attempts to change the value of a computed column does, however, fire any triggers associated with the column.

       For syntax, see CREATE TABLE statement. For information on how to use computed columns with Java objects, see Using computed columns with Java classes.

• SETUSER statement     This statement allows database administrators to act as if they were a user with another user ID. Also, you can use the SETUSER statement for connection pooling in application server environments.

       For more information, see SETUSER statement.

• UNLOAD TABLE     When unloading columns of binary data types, UNLOAD TABLE now writes out hexadecimal strings, rather than binary strings. LOAD TABLE works with either the old or the new format.