SQL statements

Following is a list of SQL enhancements introduced in SQL Anywhere version 11.0.0.

  • New CALIBRATE GROUP READ clause, ALTER DATABASE statement   The new CALIBRATE GROUP READ clause of the ALTER DATABASE statement allows you to perform group read calibration on the temporary dbspace. See ALTER DATABASE statement.

  • New CHECK clause, CREATE MATERIALIZED VIEW statement   The new CHECK clause of the CREATE MATERIALIZED VIEW statement allows you to validate the statement before creating the view. See CREATE MATERIALIZED VIEW statement.

  • New RECOMPILE clause, ALTER FUNCTION statement   A new clause, RECOMPILE, has been added to the ALTER FUNCTION statement to allow you to recompile a user-defined function. See ALTER FUNCTION statement.

  • New RECOMPILE clause, ALTER PROCEDURE statement   A new clause, RECOMPILE, has been added to the ALTER PROCEDURE statement to allow you to recompile a stored procedure. See ALTER PROCEDURE statement.

  • New REFRESH clause, ALTER MATERIALIZED VIEW statement   A new clause, REFRESH, has been added to the ALTER MATERIALIZED VIEW statement to allow you to specify the refresh type for the materialized view. See ALTER MATERIALIZED VIEW statement.

  • LOAD TABLE statement enhancements in support of recovery and mirroring   The following clauses have been added to the LOAD TABLE statement in support of recovery and mirroring:

    • WITH CONTENT LOGGING clause   The WITH CONTENT LOGGING clause instructs the database server to record the contents of the data source in the transaction log. The data is recorded in small chunks as the input is processed by LOAD TABLE. These chunks can be reconstituted into rows by a mirroring database, or when recovering from the transaction log. The WITH CONTENT LOGGING clause can be beneficial when it is not desirable to maintain the original data files for later recovery. See LOAD TABLE statement.

    • WITH ROW LOGGING clause   The WITH ROW LOGGING clause instructs the database server to record, as a series of INSERT statements, all the rows being loaded. This level is ideal for databases involved in synchronization, as well as in situations where the table being loaded into contains non-deterministic values, such as computed columns, or CURRENT TIMESTAMP defaults. See LOAD TABLE statement.

    • WITH FILE NAME LOGGING clause   The WITH FILE NAME LOGGING clause instructs the database server to record only the LOAD TABLE statement. This is the default behavior and is consistent with the logging behavior in previous versions of SQL Anywhere. See LOAD TABLE statement.

  • New client file loading and unloading clauses   The enhancements have been made to the LOAD TABLE and UNLOAD TABLE statements in support of the new client file loading/unloading feature:

    • New USING CLIENT FILE clause for the LOAD TABLE statement   Allows you to load a table using data in a file located on the client computer. See LOAD TABLE statement.

    • New INTO CLIENT FILE clause for the UNLOAD TABLE statement   Allows you to specify a file on the client computer to unload data into. See UNLOAD statement.

  • New login policy statements   The following statements have been added in support of the new login policy feature:

  • New full text search statements and clauses   The following statements have been added in support of the new full text search feature:

    • New CONTAINS search condition   The CONTAINS search condition is used to check a specified list of columns for the existence of a specified list of terms or phrases. The CONTAINS search condition returns either TRUE or FALSE. When searching for multiple terms or phrases, you can combine them with various Boolean operators. See CONTAINS search condition.

    • New CONTAINS clause in the FROM clause of a SELECT statement   The CONTAINS clause is specified in the FROM clause of a SELECT statement and works much like the CONTAINS search condition but also returns a score for each matching column, and an overall score for each matching row. See FROM clause.

    • CREATE TEXT CONFIGURATION statement   This statement creates a text configuration object. A text configuration object is a set of configuration settings that control characteristics of a text index. See CREATE TEXT CONFIGURATION statement.

    • ALTER TEXT CONFIGURATION statement   This statement alters a text configuration object. See ALTER TEXT CONFIGURATION statement.

    • DROP TEXT CONFIGURATION statement   This statement drops a text configuration object. See DROP TEXT CONFIGURATION statement.

    • CREATE TEXT INDEX statement   This statement creates a text index. A text index stores complete positional information for every instance of every term in every indexed column. See CREATE TEXT INDEX statement.

    • ALTER TEXT INDEX statement   This statement alters a text index. See ALTER TEXT INDEX statement.

    • DROP TEXT INDEX statement   This statement removes a text index from the database. See DROP TEXT INDEX statement.

    • REFRESH TEXT INDEX statement   This statement refreshes a text index. See REFRESH TEXT INDEX statement.

    • TRUNCATE TEXT INDEX statement   This statement truncates the data from a text index. See TRUNCATE TEXT INDEX statement.

  • ALTER EVENT statement enhancement   You can now hide the definition for an event handler using the ALTER EVENT ... SET HIDDEN statement. This statement results in the obfuscation of the event handler definition stored in the action column of the ISYSEVENT system table. See ALTER EVENT statement.

  • BEGIN SNAPSHOT statement   The BEGIN SNAPSHOT statement lets you control when a snapshot starts for snapshot isolation. See BEGIN SNAPSHOT statement.

  • CASE statement and CASE expression enhancements   For improved compatibility, CASE statements and CASE expressions are now permitted to end with either END or END CASE. See CASE statement and CASE expressions.

  • COMMENT statement enhancements   You can now add comments to the login policies table and to dbspaces. See:

  • CREATE MATERIALIZED VIEW statement enhancement   You can now create a materialized view that is refreshed whenever underlying data changes, using the new IMMEDIATE REFRESH clause of the CREATE MATERIALIZED VIEW statement. See CREATE MATERIALIZED VIEW statement.

  • DESCRIBE statement enhancement   The Interactive SQL DESCRIBE statement now allows you to obtain information about the database or database server that Interactive SQL is connected to. See DESCRIBE statement [Interactive SQL].

  • IF statement and IF expression enhancements   For improved compatibility, IF statements and IF expressions are now permitted to end with either ENDIF or END IF. See IF statement and IF expressions.

  • LOAD TABLE statement enhancements   When using the LOAD TABLE statement, you can now specify whether the data in the input file is compressed, and/or encrypted, using the new COMPRESSED or ENCRYPTED clauses. See LOAD TABLE statement.

  • SELECT statement enhancements  

    • Enhancements to INDEX clause   When specifying an index hint using the INDEX clause, you can now specify up to four indexes that the database server must use. See FROM clause.

    • New INDEX ONLY clause   When specifying an index hint using the INDEX clause, you can optionally specify the INDEX ONLY clause to control whether the database server performs an index-only retrieval (that is, only index data is used to satisfy the query). See FROM clause.

    • New CROSS APPLY and OUTER APPLY clauses   The SELECT statement has been extended to support apply expressions (specifically, the CROSS APPLY and OUTER APPLY clauses) in the FROM clause. An apply expression is an easy way to specify joins where the right side is dependent upon the left. For example, you can use an apply expression to evaluate a procedure or derived table once for each row in a table expression. See Joins resulting from apply expressions and FROM clause.

    • New OPENSTRING clause   Using the new OPENSTRING clause, you can now use a SELECT statement to query data in a file. See FROM clause.

  • Specify an owner when creating, altering, dropping, or commenting on events   The CREATE EVENT, ALTER EVENT, DROP EVENT, and COMMENT ON EVENT statements now allow you to optionally specify the owner. See:

  • UNLOAD statement enhancements   When using the UNLOAD statement, you can now specify whether to compress and/or encrypt the data that is being unloaded by specifying the COMPRESSED or ENCRYPTED clauses, respectively. See UNLOAD statement.

    Files compressed or encrypted using these clauses can only be loaded (for example, using LOAD TABLE) by SQL Anywhere 11.0.0 database servers (or newer). Files compressed or encrypted using other tools are not usable by SQL Anywhere.

  • UPDATE statement enhancement   For both search and positioned updates, you can now use the SET clause to set the column value to its default value. See UPDATE statement and UPDATE (positioned) statement [ESQL] [SP].

  • Extension to the OPTION clause   The OPTION clause for the INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements can now override the setting of the user_estimates database option. See:

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1