SQL statements

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

Improved qualification for named index hints A PRIMARY KEY index and a FOREIGN KEY index on a table can have the same name. When this is true, a named index hint can not be unambiguously specified. The specification of a named index hint has been extended to allow the qualification of a hinted index as a PRIMARY KEY index or a FOREIGN KEY index. See INDEX clause, FROM clause.
New IS DISTINCT FROM and IS NOT DISTINCT FROM search conditions The IS DISTINCT FROM and IS NOT DISTINCT FROM search conditions allow you to control the evaluation of equality in the case of NULLs. See IS DISTINCT FROM and IS NOT DISTINCT FROM search conditions.
CREATE SYNCHRONIZATION PROFILE statement This statement creates a SQL Anywhere synchronization profile. A synchronization profile defines how a SQL Anywhere database synchronizes with the MobiLink server. See CREATE SYNCHRONIZATION PROFILE statement [MobiLink].
New WITH NULLS NOT DISTINCT clause, CREATE INDEX statement A new clause, WITH NULLS NOT DISTINCT, has been added to the CREATE INDEX statement for use when creating UNIQUE indexes. This clause allows you to specify that NULLs in index keys are not unique. You must upgrade or rebuild existing databases to use this feature. See the UNIQUE clause of the CREATE INDEX statement.
Back quote identifier delimiter Back quotes can now be used as an identifier delimiter. See Identifiers.
New SAVE OPTION VALUES clause for ALTER TEXT CONFIGURATION statement Use this new clause to change the values of the date_format, time_format, timestamp_format, and timestamp_with_time_zone_format options saved with a text configuration object. See ALTER TEXT CONFIGURATION statement.
ALTER SERVER and CREATE SERVER statements The new IQODBC and IQJDBC server classes allow you to specify a Sybase IQ server as a remote connection. See ALTER SERVER statement and CREATE SERVER statement.
New LOCK FEATURE statement This statement prevents other concurrent connections from using a database server feature. See LOCK FEATURE statement.
Enhancements to the BEGIN, CREATE VARIABLE, and DECLARE statements Variable declarations can now include an initial starting value for the variable. See:
- BEGIN statement
- CREATE VARIABLE statement
- DECLARE statement
Enhancements to the LOAD TABLE statement The LOAD TABLE statement now supports the load-option clause, which allows you to control how data is loaded. It also supports the value XML in the FORMAT clause. See LOAD TABLE statement.
New IF NOT EXISTS clause With the new IF NOT EXISTS clause, no changes are made and an error is not returned if the named object already exists. See:
- CREATE INDEX statement
- CREATE PUBLICATION statement [MobiLink] [SQL Remote]
- CREATE SPATIAL REFERENCE SYSTEM statement
- CREATE TABLE statement
- CREATE TEXT INDEX statement
New IF EXISTS clause The new IF EXISTS clause allows you to specify that you do not want an error returned when the DROP statement attempts to remove a database object that does not exist. See:
- DROP EVENT statement
- DROP FUNCTION statement
- DROP INDEX statement
- DROP MATERIALIZED VIEW statement
- DROP PROCEDURE statement
- DROP PUBLICATION statement [MobiLink] [SQL Remote]
- DROP SPATIAL REFERENCE SYSTEM statement
- DROP SPATIAL UNIT OF MEASURE statement
- DROP SYNCHRONIZATION PROFILE statement [MobiLink]
- DROP TABLE statement
- DROP TRIGGER statement
- DROP VARIABLE statement
- DROP VIEW statement
CASE statement enhancements CASE statements are now supported in Transact-SQL procedures and batches.
New OR REPLACE clause The new OR REPLACE clause allows you to create or replace a profile or variable of the same name. See:
- CREATE FUNCTION statement (external procedures)
- CREATE FUNCTION statement (web clients)
- CREATE FUNCTION statement
- CREATE MIRROR SERVER statement
- CREATE PROCEDURE statement (external procedures)
- CREATE PROCEDURE statement (web clients)
- CREATE PROCEDURE statement [T-SQL]
- CREATE PROCEDURE statement
- CREATE SEQUENCE statement
- CREATE SPATIAL REFERENCE SYSTEM statement
- CREATE SYNCHRONIZATION PROFILE statement [MobiLink]
- CREATE TRIGGER statement
- CREATE VARIABLE statement
- CREATE VIEW statement
New LIMIT clause support for SELECT statements You can now specify row counts and offsets in a SELECT statement using the new LIMIT clause. See SELECT statement.
Enhancement to the SET OPTION statement Syntax 1 of the SET OPTION statement now supports setting an option using the contents of a variable. See SET OPTION statement.
New IF [ NOT] OF search condition The IF [NOT] OF type-expression search condition has been added. See Search conditions and Spatial data type syntax based on ANSI SQL UDTs.

INSERT statement enhancements The following enhancements have been made to the INSERT statement. See INSERT statement.

Support for more than one list of values An INSERT statement can now contain more than one list of values, allowing several rows to be inserted at once. For example:

INSERT INTO T (c1,c2,c3) VALUES (1,10,100), (2,20,200), (3,30,300);

Support for inserting rows with all default values SQL Anywhere allows the VALUES clause to contain specified values for a subset of the columns in the table. All unspecified columns are given default values as specified for each column by means of DEFAULT, NULL and COMPUTE clauses of the CREATE TABLE statements. Previously, the database server required that you specify input values for at least one of the columns in the table.
Now, however, all columns can be given their default values by using either of the following syntax extensions:
```
INSERT [ INTO ] table-name options DEFAULT VALUES ...
```
```
INSERT [ INTO ] table-name ( ) options VALUES ( ) [ , ( ) ... ]
```
Specifying DEFAULT VALUES or VALUES is semantically equivalent to using the following syntax, where the number of default entries is equal to the number of columns in the table:
```
INSERT [ INTO ] table-name VALUES( default, default, ..., default )
```
The DEFAULT VALUES clause is part of the SQL/2008 standard, whereas the VALUES clause is a vendor extension.

New START SERVER statement The START SERVER statement has been added. It should be used instead of the START ENGINE statement, which is now deprecated. See START SERVER statement [Interactive SQL].
New STOP SERVER statement The STOP SERVER statement has been added. It should be used instead of the STOP ENGINE statement, which is now deprecated. See STOP SERVER statement.
New CHECKSUM clause for the ALTER DATABASE statement The CHECKSUM OFF clause lets you disable global checksums for databases. Global checksums are enabled by default for new version 12 databases. See ALTER DATABASE statement.
DELETE statement enhancement The DELETE statement now supports correlation names. See DELETE statement.

Discuss this page in DocCommentXchange.