SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

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.

IGNORE_DUP_ROW allows the creation of a
clustered index on tables that contain duplicate rows. While the
index can be created when the table contains duplicate rows, any
insert or update statement that can result in the creation of a
duplicate row will be canceled once the index is created.

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:

The TO
clause cannot be used in place of
FROM.

The AS clause is
not permitted.

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.