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

Discuss this page in DocCommentXchange.