SQL Anywhere has date, time and timestamp types that include a year, month and day, hour, minutes, seconds, and fraction of a second. For insertions or updates to date fields, or comparisons with date fields, a free format date is supported.

In addition, the following operations are allowed on dates:

SQL Anywhere does not support an INTERVAL data type, which is SQL language feature F052 of the SQL/2008 standard. However, SQL Anywhere provides many functions, such as DATEADD, for manipulating dates and times. See Date and time functions.

SQL Anywhere supports both entity and referential integrity via the PRIMARY KEY and FOREIGN KEY clauses of the CREATE TABLE and ALTER TABLE statements.

PRIMARY KEY [ CLUSTERED ] ( column-name [ ASC | DESC ], ... )
[NOT NULL] FOREIGN KEY [role-name]
            [(column-name [ ASC | DESC ], ...) ]
         REFERENCES table-name [(column-name, ...) ]
            [ MATCH [ UNIQUE | SIMPLE | FULL ] ]
            [ ON UPDATE [ CASCADE | RESTRICT | SET DEFAULT | SET NULL ] ] 
            [ ON DELETE [ CASCADE | RESTRICT | SET DEFAULT | SET NULL ] ] 
            [ CHECK ON COMMIT ] [ CLUSTERED ]

The PRIMARY KEY clause declares the primary key for the table. SQL Anywhere then enforces the uniqueness of the primary key by creating a unique index over the primary key column(s). Two SQL Anywhere extensions permit the customization of this index:

The FOREIGN KEY clause defines a relationship between two tables. This relationship is represented by a column (or columns) in this table that must contain values in the primary key of another table. SQL Anywhere automatically constructs an index for each FOREIGN KEY defined to enforce the referential constraint. The semantics of the constraint, and physical characteristics of this index, can be customized as follows:

For more information, see CREATE TABLE statement.

SQL Anywhere supports the creation of unique indexes, sometimes called unique secondary indexes, over nullable columns. By default, each index key must be unique or contain a NULL in at least one column. For example, two index entries ('a', NULL) and ('a', NULL) are each considered unique index values. SQL Anywhere also supports unique secondary indexes where NULL values are treated as special values in each domain. This is accomplished using the WITH NULLS NOT DISTINCT clause. With such an index, the two pairs of values ('a', NULL) and ('a', NULL) are considered duplicates.

For more information, see CREATE INDEX statement.

SQL Anywhere supports INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER joins. In addition to explicit join predicates, SQL Anywhere supports NATURAL joins and a vendor extension known as KEY joins, which specifies an implicit join predicate based on the tables' foreign key relationships.

For more information about key joins, see Key joins.

SQL Anywhere internals do not distinguish between fixed- and varying-length string types (CHAR, NCHAR, or BINARY). SQL Anywhere does not truncate trailing blanks from string types when such values are inserted to the database. SQL Anywhere does distinguish between the NULL value and the empty string. By default, SQL Anywhere databases utilize a case-insensitive collation to support case-insensitive string comparisons. In SQL Anywhere, fixed-length string types are never blank-padded; rather, blank-padding semantics are simulated during the execution of each string comparison. These semantics may differ subtly from string comparisons with other SQL implementations.

For more information, see Character data types.

SQL Anywhere partially supports optional SQL language feature T111 that permits an UPDATE statement to refer to a view that contains a join. In addition, the UPDATE and UPDATE WHERE CURRENT OF statements permit more than one table to be referenced in the statement's SET clause, and the FROM clause of an UPDATE statement can be comprised of an arbitrary table expression containing joins and derived tables.

SQL Anywhere also allows the UPDATE, INSERT, MERGE, and DELETE statements to be embedded within another SQL statement as a derived table. One of the benefits of this support is that you can construct a query that returns the set of rows that has been modified by an UPDATE statement in a straightforward way.

For more information about DML derived tables, see FROM clause.

SQL Anywhere lets you refer to the result set of a stored procedure as a table in a statement's FROM clause, a feature commonly referred to as table functions. Table functions are SQL language feature T326 of the SQL/2008 standard. In the standard, table functions are specified using the TABLE keyword. In SQL Anywhere, use of the TABLE keyword is unnecessary; a stored procedure can be referenced directly in the FROM clause, optionally with a correlation name and a specification of schema of the result set returned by the procedure.

The following example joins the result of the stored procedure ShowCustomerProducts with the base table Products. Accompanying the stored procedure reference is an explicit declaration of the schema of the procedure's result, using the WITH clause:

SELECT sp.ident, sp.quantity, Products.name
FROM ShowCustomerProducts( 149 ) WITH ( ident INT, description CHAR(20), quantity INT ) sp
   JOIN Products ON sp.ident = Products.ID

For more information about table functions, see FROM clause.

SQL Anywhere supports materialized views, which are precomputed result sets that can be referenced directly or indirectly from within a SQL query. In SQL Anywhere, both immediately-maintained and manually-maintained views can be created using the CREATE MATERIALIZED VIEW statement. Other database products may use different terms to describe this functionality.

For more information on materialized views, see Working with materialized views.

SQL Anywhere supports optional SQL language feature F431 of the SQL/2008 standard. In SQL Anywhere, all cursors are bi-directionally scrollable unless they are explicitly declared FORWARD ONLY, and applications can scroll through a cursor using either relative or absolute positioning with the FETCH statement or its equivalent with other application programming interfaces, such as ODBC.

SQL Anywhere supports value-sensitive and row-membership sensitive cursors. Commonly-supported cursor types, including INSENSITIVE, KEYSET-DRIVEN, and SENSITIVE cursors, are supported. When using embedded SQL, cursor positions can be moved arbitrarily on the FETCH statement. Cursors can be moved forward or backward relative to the current position or a given number of records from the beginning or end of the cursor.

For more information about cursors and cursor sensitivity, see SQL Anywhere cursors.

By default, cursors in embedded SQL and SQL procedures, user-defined functions, and triggers are updatable. They can be made explicitly updatable by using the FOR UPDATE clause. However, specifying the FOR UPDATE clause alone does not acquire any locks on the rows in the cursor's result set. To ensure that rows in the result set cannot be modified by other transactions, you can specify either:

For more information about cursor updatability, see DECLARE CURSOR statement [ESQL] [SP].

SQL Anywhere permits aliased expressions in the select list of a query to be referenced in other parts of the query. Most other SQL implementations and the SQL/2008 standard do not allow this behavior. For example, you can specify the SQL query:

SELECT column-or-expression AS alias-name
FROM table-reference
WHERE alias-name = expression

Aliases can be used anywhere in the SELECT block, including other select list expressions that in turn define additional aliases. Cyclic alias references are not permitted. If the alias specified for an expression is identical to the name of a column or variable in the name space of the SELECT block, the alias definition occludes the column or variable. Column names, however, can be explicitly qualified by table name in such cases.

SQL Anywhere supports snapshot isolation, which is also known as Multi-Version Concurrency Control, or MVCC. In other SQL implementations that support snapshot isolation, writer-writer conflicts - that is, concurrent updates by two or more transactions to the same row - are made apparent only at the time of COMMIT. In such cases, usually the first COMMIT wins, and the other transactions involved in the conflict must abort.

In SQL Anywhere, write operations to rows cause write row locks to be acquired so that snapshot transactions can co-exist with transactions executing at ANSI isolation levels. Consequently, a writer-writer conflict in SQL Anywhere will result in blocking, though the precise behavior can be controlled through the BLOCKING and BLOCKING_TIMEOUT connection options.

For more information on snapshot isolation, see Snapshot isolation.