Sybase IQ features

The following Sybase IQ features are not found in many other SQL implementations.

Dates

Sybase IQ has date, time, and timestamp types that include year, month, 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:

Also, many functions are provided for manipulating dates and times. See Chapter 4, “SQL Functions” for a description of these.

Integrity

Sybase IQ supports both entity and referential integrity. This has been implemented via the following two extensions to the CREATE TABLE and ALTER TABLE commands.

PRIMARY KEY ( column-name, ... )
[NOT NULL] FOREIGN KEY [role-name] 
				[(column-name, ...)]
			REFERENCES table-name [(column-name, ...)]
				[ CHECK ON COMMIT ]

The PRIMARY KEY clause declares the primary key for the relation. Adaptive Server IQ will then enforce the uniqueness of the primary key, and ensure that no column in the primary key contains the NULL value.

The FOREIGN KEY clause defines a relationship between this table and another table. This relationship is represented by a column (or columns) in this table which must contain values in the primary key of another table. The system then ensures referential integrity for these columns; whenever these columns are modified or a row is inserted into this table, these columns are checked to ensure that either one or more is NULL or the values match the corresponding columns for some row in the primary key of the other table. For more information, see CREATE TABLE statement.

Joins

Sybase IQ allows automatic joins between tables. In addition to the NATURAL and OUTER join operators supported in other implementations, Sybase IQ allows KEY joins between tables based on foreign-key relationships. This reduces the complexity of the WHERE clause when performing joins.

Updates

Sybase IQ allows more than one table to be referenced by the UPDATE command. Views defined on more than one table can also be updated. Many SQL implementations do not allow updates on joined tables.

Altering tables

The ALTER TABLE command has been extended. In addition to changes for entity and referential integrity, the following types of alterations are allowed:

ADD column data-type
MODIFY column data-type
DELETE column
RENAME new-table-name
RENAME old-column TO new-column

You can use MODIFY to change the maximum length of a character column, as well as converting from one data type to another. See “ALTER TABLE statement,” in “SQL Statements,”in Reference: Statements and Options.

Subqueries not always allowed

Unlike SQL Anywhere, Sybase IQ does not allow subqueries to appear wherever expressions are allowed. Sybase IQ supports subqueries only as allowed in the SQL-1989 grammar, plus in the SELECT list of the top level query block or in the SET clause of an UPDATE statement. Sybase IQ does not support SQL Anywhere extensions.

Many SQL implementations allow subqueries only on the right side of a comparison operator. For example, the following command is valid in Sybase IQ but not valid in most other SQL implementations.

SELECT		SurName,
			BirthDate,
			(	SELECT DepartmentName 
				FROM Departments
				WHERE DepartmentID = Employees.EmployeeID
				AND DepartmentID = 200 )
FROM Employees

Additional functions

Sybase IQ supports several functions not in the ANSI SQL definition. See Chapter 4, “SQL Functions” for a full list of available functions.

Cursors

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.