A.2 Sybase Adaptive Server SQL Statements

     

As we saw in the previous section, there is significant overlap in the type support offered by Sybase and SQL Server. This section compares the SQL dialects of Sybase to SQL Server. In most cases, the SQL Server statements described in Chapter 3 apply to Sybase as well. This section outlines those statements that are different between the two platforms.

In Table A-2, the SQL commands are listed along with notes containing the differences between SQL Server and Sybase. While most of the statements have identical syntax and meaning, for those statements that contain large differences, the syntax for Sybase is listed with notes on how the statements differ .

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]}[,...]] [{ENABLEDISABLE} 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( ) f unction 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 ][LRUMRU])]}[,...] [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.