Table A-2. Sybase Adaptive Server SQL statements
Command
|
Notes
|
---|
ALTER PROCEDURE
|
Sybase does not support the
ENCRYPTION keyword or replication
through the FOR REPLICATION keywords.
|
ALTER TABLE
|
Sybase does not support the ROWGUIDCOL keyword
or the CHECK and NOCHECK
keywords for enabling constraints.
ALTER TABLE table_name
[ADD {column_name datatype attributes}[,...]]
| [DROP column_name[,...]]
| [ADD CONSTRAINT {constraint_name constraint_clause}[,...]]
| [DROP CONSTRAINT constraint_name[,...]]
| [MODIFY {column_name data_type [[NOT] NULL]}[,...]]
| [{ENABLE|DISABLE} trigger_name]
|
ALTER TRIGGER
|
Sybase has no equivalent to the ALTER TRIGGER
statement in SQL Server. For equivalent functionality, drop the
existing trigger with a DROP TRIGGER statement
and recreate it with the CREATE
TRIGGER statement.
|
ALTER VIEW
|
Sybase has no equivalent to the ALTER VIEW
statement in SQL Server. For equivalent functionality, drop the
existing view with a DROP VIEW statement and
recreate it with the CREATE VIEW statement.
|
CALL
|
Not supported by SQL Server or Sybase. Look to the
EXECUTE command for this functionality.
|
CONNECT
|
Supported with variations.
CONNECT TO server_name
|
CREATE DATABASE
|
Creating a new database in Sybase has the following syntax:
CREATE DATABASE database_name
[ON { DEFAULT | database_device} [=size]
[, {database_device [=size]}[,...]]
[LOG ON {log_device [=size]}[,...]
[WITH {OVERRIDE | DEFAULT_LOCATION = "pathname"}]
[FOR {LOAD | PROXY_UPDATE}]
DEFAULT specifies the default device, equivalent
to the PRIMARY keyword on SQL Server. size is the size of the database or transaction
log in megabytes. log_device has the same meaning as SQL Server. PROXY_UPDATE automatically gets the metadata
from the DEFAULT_LOCATION when creating the
database.
|
CREATE FUNCTION
|
Support is identical to that offered by SQL Server, except that the
following options are unsupported: VARYING,
ENCRYPTION, and FOR
REPLICATION.
|
CREATE INDEX
|
Differences exist within the options provided in the
WITH clause, as follows:
[WITH [{FILLFACTOR = fillfactor |
MAX_ROWS_PER_PAGE = num_rows}]
[[,]RESERVEPAGEGAP = num_pages]
[[,]CONSUMERS = X]
[[,]IGNORE_DUP_KEY]
[[,]IGNORE_DUP_ROW | ALLOW_DUP_ROW]
[[,]SORTED_DATA]
[[,]STATISTICS USING num_steps VALUES]
|
|
FILLFACTOR has the same meaning as SQL Server. MAX_ROWS_PER_PAGE is another way of controlling
the fill factor of the index, but uses the row count instead of a
percentage of the page space. RESERVEPAGEGAP provides a method of controlling
the ratio of empty index pages to filled ones. This can provide a
performance advantage for indexes that grow in size frequently. Valid
values for num_pages are 0-255, with the
default being 0. IGNORE_DUP_KEY has the same meaning as SQL
Server.
|
|
|
CREATE INDEX
|
ALLOW_DUP_ROW is similar to
IGNORE_DUP_ROW, except that it permits insert
and update statements that will result in duplicate rows. SORTED_DATA speeds up the creation of indexes
for tables that are already sorted on disk. WITH STATISTICS USING num_steps VALUES controls
the amount of statistics maintained and provided to the query
optimizer.
|
CREATE PROCEDURE
|
Support is identical to that offered by SQL Server, except that the
following options are unsupported: VARYING,
ENCRYPTION, and FOR
REPLICATION.
|
CREATE ROLE
|
Supported with variations.
CREATE ROLE role_name [WITH PASSWD "password"
[, {"PASSWD EXPIRATION" | "MIN PASSWD LENGTH" |
"MAX FAILED LOGINS" } option_value ][,...] ]
This statement will create a role by role_name
with an optional password. Unique to Sybase is the ability to specify
simple security options for the new role.
|
CREATE TABLE
|
While casual users of SQL Server and Sybase will notice no
differences in the CREATE TABLE syntax, others will note the
following differences:
The NOT FOR REPLICATION option is not supported on Sybase. The ROWGUIDCOL column attribute is not supported on Sybase. The TEXTIMAGE_ON option is not supported on Sybase. The IDENTITY attribute cannot have a seed or increment value
specified on Sybase.
Other than those small changes, the features unique to Sybase are
listed below.
CREATE TABLE [database_name.[owner].]table_name
({column_name datatype
{[DEFAULT default_value]
| [IDENTITY | NULL | NOT NULL]
| [OFF ROW | IN ROW [ (size_in_bytes) ] ]
| REFERENCES [[database_name.]owner.]ref_table [(ref_column)]
{UNIQUE | PRIMARY KEY} [CLUSTERED | NONCLUSTERED] [asc |
desc]
[WITH { FILLFACTOR = pct,
MAX_ROWS_PER_PAGE = num_rows, }
RESERVEPAGEGAP = num_pages }]
[ON segment_name]
| CHECK (search_condition)
}
|[CONSTRAINT constraint_name]
|FOREIGN KEY ({column_name}[,...]) REFERENCES
[[database_name.]owner.]ref_table
[({ref_column}[,...])]
| CHECK (search_condition)
|{UNIQUE | PRIMARY KEY} [CLUSTERED | NONCLUSTERED]
({column_name [ASC | DESC]}[,...])
[WITH { FILLFACTOR = pct,
MAX_ROWS_PER_PAGE = num_rows,
RESERVEPAGEGAP = num_pages } ]
[ON segment_name]}[,...])
[LOCK {DATAROWS | DATAPAGES | ALLPAGES }]
[WITH { MAX_ROWS_PER_PAGE = num_rows,
EXP_ROW_SIZE = num_bytes,
RESERVEPAGEGAP = num_pages,
IDENTITY_GAP = value }]
[ON segment_name ]
[ [ EXTERNAL TABLE ] AT pathname ]
|
CREATE TABLE
|
OFF/IN ROW specifies if a Java-SQL column is
physically stored inside a row or outside. The
SIZE_IN_BYTES option is the maximum space
required to store an IN ROW type. ASC/DESC determines the ordering for an index
created for a constraint. Ascending order, ASC,
is the default. MAX_ROWS_PER_PAGE limits the number of rows per
page. LOCK specifies the locking strategy used for the
table. EXP_ROW_SIZE specifies the expected row size in
bytes. The default is zero, which means the server's
default will be used. RESERVEPAGEGAP specifies the desired ratio of
filled to empty pages. Valid values are 0-255, with a default of
zero. IDENTITY_GAP controls the gap between
consecutive values in identity columns. EXTERNAL TABLE specifies that the table is
stored externally. This is the default, so its usage is optional.
|
CREATE TRIGGER
|
Sybase offers support similar to SQL Server, with the following
exceptions:
WITH ENCRYPTION is not permitted. AFTER and INSTEAD OF are
not permitted. WITH APPEND is not permited. NOT FOR REPLICATION is not
permitted. COLUMNS_UPDATED( ) function
is not supported.
|
CREATE VIEW
|
Support is identical to that offered by SQL Server except that Sybase
has no ENCRYPTION,
SCHEMABINDING, or
VIEW_METADATA options.
|
DECLARE CURSOR
|
Support is identical to that offered by SQL Server, except that
Sybase has no INSENSITIVE or
SCROLL options.
|
DELETE
|
The DELETE statement is nearly identical between
Sybase and SQL Server. SQL Server supports a
WITH and OPTION clause that
Sybase does not. The features unique to Sybase are listed below.
DELETE [[owner.]{table_name | view_name}]
[FROM {[owner.]
{view_name [READPAST]
|table_name [READPAST]
[(INDEX {index_name|table_name}
[PREFETCH size][LRU | MRU])]
}}[,...]
[WHERE {search_conditions | CURRENT OF cursor_name}] ]
[PLAN "abstract_plan"]
READPAST instructs the server to skip over all
pages or rows currently locked by other transactions, deleting rows
only from pages not currently in use.
PREFETCH specifies the I/O size, in kilobytes,
for tables that are bound to caches.
LRU/MRU specifies either a least or most
recently used buffer replacement strategy.
PLAN sends abstract_plan as
an alternative execution plan to the query optimizer.
|
DISCONNECT
|
Supported with the following command:
DISCONNECT
|
DROP ROLE
|
Supported with the following syntax:
DROP ROLE role_name [WITH OVERRIDE]
Using WITH OVERRIDE will ignore all restrictions
on dropping the role from the databases.
|
FETCH
|
The FETCH command differs significantly from SQL
Server. Sybase has sequential server-side cursors, so the
NEXT, PRIOR,
FIRST, and LAST options are
not permitted.
Additionally, parameters can be used in a target list as long as
there is a one-to-one mapping between these items and the ones
returned by the SELECT statement when the cursor
was created.
FETCH cursor_name [INTO fetch_target_list]
For example:
DECLARE authors_cursor CURSOR
FOR SELECT au_lname, au_fname FROM authors
OPEN authors_cursor
FETCH authors_cursor INTO @lname, @fname
GO
|
GRANT
|
Support is identical to that offered by SQL Server, except that
Sybase has no AS clause.
|
INSERT
|
Sybase offers the SQL99 syntax for INSERT
statements; therefore, Sybase permits none of the extended features
of SQL Server.
|
RETURN
|
Sybase's return statement
RETURN, unlike SQL Server, cannot return NULL
values.
|
REVOKE
|
Sybase offers support similar to SQL Server with the following
exceptions:
|
SAVEPOINT
|
Support identical to SQL Server's SAVE
TRANSACTION statement.
|
SELECT
|
Sybase offers support for SELECT statements
identical to SQL Server, with the following exceptions:
Sybase has a PLAN clause instead of an
OPTION clause used for passing hints to the
server's statement optimizer. Sybase has no TOP clause. Sybase does not support WITH CUBE or
WITH ROLLUP in the
GROUP BY clause. Sybase offers a FOR { UPDATE | READ ONLY }
clause that can only be used with a stored procedure when the query
defines the result for a cursor. Sybase has AT ISOLATION { 0, 1, 2, 3 } for
choosing a non-default isolation level for query execution. Sybase also offers a SELECT INTO statement, but
with an additional, optional clause for controlling the locking:
LOCK { DATAROWS | DATAPAGES | ALLPAGES }
|
SET ROLE
|
Sybase supports the SET ROLE statement with the
following syntax:
SET ROLE {"SA_ROLE" | "SSO_ROLE" | "OPER_ROLE" | role_name
[WITH PASSWD "password"]} { ON | OFF }
|
SET TIME ZONE
|
The SET TIMEZONE statement is not supported in
Sybase or SQL Server.
|
START TRANSACTION
|
Support is offered through BEGIN TRANSACTION,
which is identical to SQL Server, except that parameterized
transaction names are not supported.
|
UPDATE
|
The UPDATE statement is nearly identical between
Sybase and SQL Server. SQL Server supports WITH
and OPTION clauses that Sybase does not. The
features unique to Sybase are listed below.
UPDATE {table_name | view_name}
SET {[{table_name.|view_name.}]
column_name1 = {expression1|NULL|(select_statement)} |
variable_name1 = {expression1|NULL|(select_statement)}}
[,...]
[FROM {view_name [READPAST]|table_name [READPAST]
[(INDEX {index_name | table_name }
[ PREFETCH size ][LRU|MRU])]}[,...]
[WHERE {search_conditions | CURRENT OF cursor_name}]
[PLAN "abstract_plan"]
|
UPDATE
|
READPAST instructs the server to skip over all
pages or rows currently locked by other transactions, updating rows
only from pages not currently in use. PREFETCH specifies the I/O size, in kilobytes,
for tables that are bound to caches. LRU/MRU specifies either the least or most
recently used buffer replacement strategy. PLAN sends abstract_plan as
an alternative execution plan to the query optimizer.
|