Transaction Management

Both Sybase and Microsoft support explicit transactions managed with the statements BEGIN TRANSACTION, SAVE, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.

Rollback Trigger

The Sybase ROLLBACK TRIGGER statement rolls back only the work performed by the statement that fired the trigger.

In Microsoft SQL Server version 7.0, you must replace the ROLLBACK TRIGGER statements with paired SAVE TRANSACTION (tr1)… ROLLBACK TRANSACTION (tr1) statements to roll back a single Transact-SQL statement without affecting the rest of the transaction. Sybase applications that currently use ROLLBACK TRIGGER should be changed to issue SAVE TRANSACTION (tr1), fire the trigger, and then issue the ROLLBACK TRANSACTION (tr1) statement if needed before executing any other Transact-SQL statements.

Chained Transactions

Sybase System 10 introduced chained transactions, which are transactions that have implicit starting points but must be explicitly committed. A connection can put itself into or out of a chained transaction state with the SET statement:

SET CHAINED [ON | OFF]

Microsoft SQL Server version 6.5 introduced a similar feature called implicit transactions that functions in the same way as Sybase chained transactions. Microsoft SQL Server implicit transactions are also controlled by the SET statement:

SET IMPLICIT_TRANSACTIONS [ON | OFF]

Change the SET CHAINED statements in Sybase applications to SET IMPLICIT_TRANSACTION statements for Microsoft SQL Server.

Sybase stored procedures are tagged with the transaction mode (chained or unchained) with which they were created, while Microsoft SQL Server procedures operate in the transaction mode that exists when they are executed. Therefore, Sybase procedures can have COMMIT TRANSACTIONS that are not matched with a BEGIN TRANSACTION statement; this is not allowed in Microsoft SQL Server procedures. Scan all Sybase procedures created in chained mode for COMMIT TRANSACTION statements that do not have matched BEGIN TRANSACTION statements. Either remove the COMMIT TRANSACTION from or add a BEGIN TRANSACTION to the procedure before it is migrated to Microsoft SQL Server.

The Sybase @@tranchain variable, indicating the current transaction mode (0=unchained, 1=chained), has no Microsoft SQL Server equivalent. The 2 bits in the Microsoft SQL Server @@OPTIONS function report the mode of implicit_transactions:

IF (@@OPTIONS & 2) > 0

    PRINT 'Implicit_transactions on'

ELSE

    PRINT 'Implicit_transactions off'

The Sybase @@transtate variable, indicating whether a transaction is in progress, successful, or canceled, has no Microsoft SQL Server equivalent. Replace @@transtate logic with either @@ERROR checking or SET XACT_ABORT ON to enable Microsoft SQL Server to roll back a transaction automatically when an error occurs.

The Sybase system stored procedure sp_procxmode, used to control the transaction modes of stored procedures, has no Microsoft SQL Server equivalent. Sybase procedures must have COMMIT statements matched with BEGIN TRANSACTION statements or be removed from the stored procedures before migrating to Microsoft SQL Server.

Transaction Isolation Levels

Sybase identifies its transaction isolation levels with numbers and Microsoft SQL Server identifies the levels with character tags. Scan for SET TRANSACTION ISOLATION LEVEL statements and change the Sybase level specifications to Microsoft specifications.

Note Microsoft SQL Server 7.0 implements REPEATABLE READ in the same way as SERIALIZABLE.

Cursors

Microsoft SQL Server supports the Sybase cursor statements except for a minor difference in syntax for the DEALLOCATE CURSOR; the keyword CURSOR is not used by SQL Server with the DEALLOCATE cursor statement:

Sybase: DEALLOCATE CURSOR cursor_name
Microsoft: DEALLOCATE cursor_name

Error Checking

Error checking is implemented differently by Sybase and Microsoft SQL Server. Sybase cursors report errors through @@sqlstatus, and Microsoft reports errors through @@FETCH_STATUS. In addition, Microsoft and Sybase report different values.

Sybase allows different stored procedures to open cursors with identical names. Each cursor with the same name gets a separate result set. Microsoft SQL Server considers the scope of a cursor name to be the current session. The server does not allow different stored procedures that are executed by the same connection to open cursors with duplicate names.

Microsoft SQL Server cursors default to optimistic concurrency control, which does not place shared locks on tables. Sybase cursors generally default to pessimistic concurrency control, which places shared locks on the underlying tables. The pessimistic concurrency can reduce concurrency in high-use environments.

Index Optimizer Hints

Optimizer hints are important in Sybase implementations because Sybase does not update index statistics automatically. The Sybase query optimizer is not always reliable because it often optimizes based on outdated statistics.

Microsoft SQL Server 7.0 updates statistics automatically, so the Microsoft query optimizer is more likely than the Sybase query optimizer to make the best choice of index use. In addition, the graphical SQL Server Query Analyzer helps programmers and DBAs determine the system I/O bottlenecks. The automatically updated statistics, the accurate query optimizer, and the ability to troubleshoot using the graphical SQL Server Query Analyzer are all reasons to delete Sybase optimizer hints from the statements, not simply replace them. For more information about implementing optimizer hints, see SQL Server Books Online.

Optimizer Hints for Locking

A range of table-level locking hints can be specified by using the SELECT, INSERT, UPDATE, and DELETE statements to direct Microsoft SQL Server to the type of locks to be used. Table-level locking hints can be used when you need a finer control of the types of locks acquired on an object. These locking hints override the current transaction isolation level for the session.

The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when absolutely necessary. Disallowing a locking level can adversely affect concurrency.

To implement optimizer hints for locking manually, you must remove the System 11 hints PREFETCH, LRU, or MRU because Microsoft SQL Server does not support them. Microsoft SQL Server automatically uses READ AHEAD (RA) processing when it is appropriate. This behavior can be tailored with new RA options on sp_configure, discussed in “System Stored Procedures” later in this chapter.

For more information about locking hints, see SQL Server Books Online.

Server Roles

The Sybase server roles of sa_role, sso_role, or oper are not supported by Microsoft SQL Server. GRANT and REVOKE statements referencing these roles must be removed.

In Microsoft SQL Server, the sysadmin role has functions equivalent to the Sybase sa_role and sso_role. By using the GRANT statement, you can give individual users permissions to perform the operator actions of dumping databases and transactions, but you cannot give them permissions to load databases and transactions.

The Sybase function proc_name, which validates a user’s name, is not supported by Microsoft SQL Server and must be removed.

The table describes the fixed server roles in Microsoft SQL Server 7.0.

You can get a list of the fixed server roles from sp_helpsrvrole, and get the specific permissions for each role from sp_srvrolepermission.

Each database has a set of fixed database roles. While roles with the same names exist in each database, the scope of an individual role is only within a specific database. For example, if Database1 and Database2 both have user IDs named UserX, adding UserX in Database1 to the db_owner fixed database role for Database1 has no effect on whether UserX in Database2 is a member of the db_owner role for Database2.

The table describes the fixed database roles in Microsoft SQL Server 7.0.

Raising Errors

The Sybase version of RAISERROR allows argument substitution in any order, but the arguments must be of data type varchar or char.

The Microsoft SQL Server RAISERROR statement requires positional argument substitution, like the C language printf, but supports integer and string substitution: %d, %i, %s. This RAISERROR statement also supports the specification of a severity level (range 1 through 25).

The Microsoft RAISERROR statement includes a WITH LOG parameter so that the server enters the message in the error log. Messages raised with severities from 19 through 25 require the WITH LOG parameter.

PRINT

The Sybase version of PRINT allows argument substitution; the Microsoft version does not. The most straightforward solution is to change any Sybase PRINT that uses argument substitution to a RAISERROR with a severity of 10 or lower. Another solution is to print a string built of substrings in Microsoft SQL Server:

DECLARE @msg VARCHAR(255)

SELECT @msg = 'The object ' + @tablename + 'does not allow duplicate keys.\n'

PRINT @msg

Partitioned Tables vs. Row Locking

In Sybase 11+, partitions are supported only on user tables that do not have clustered indexes. This System 11 feature helps to reduce the blocking caused by the lack of row-level locking.

Microsoft SQL Server supports row-level locking on all table types, and does not support the keyword PARTITION on the ALTER TABLE statement.

Setting SQL-92 NULL Behavior

Both Sybase 11+ and Microsoft SQL Server version 6.5 and later support SQL-92–compliant NULL behavior. However, the syntax in the two systems is different.

Sybase:

SET ANSINULL {ON|OFF}

Microsoft SQL Server:

SET ANSI_NULLS {ON|OFF}

SET ANSI_WARNINGS {ON|OFF}

Microsoft SQL Server supports setting options that define whether columns in CREATE TABLE statements take the ANSI NULL defaults:

SET ANSI_NULL_DFLT_ON {ON|OFF}

SET ANSI_NULL_DFLT_OFF {ON|OFF}

IDENTITY Columns

Microsoft and Sybase use the same syntax for defining identity columns. The Microsoft default name for an identity column is IDENTITYCOL; the Sybase default name is SYB_IDENTITY. All references to SYB_IDENTITY must be changed to IDENTITYCOL.

SET Statement

Since their respective 4.2 versions, the SET statement options implemented by both vendors have diverged. Although the new options may have the same or similar names, their defined characteristics can be slightly different. For example, the full effect of the Sybase ANSINULLS option combines behavior defined by the Microsoft options ANSI_NULLS and ANSI_WARNINGS.

The following table lists the Sybase-specific options that do not have exact Microsoft equivalents, and lists the Microsoft options that most closely match the desired Sybase behavior. Review the SQL Server documentation carefully to understand the differences between the Sybase statement and the alternative offered. The table does not list the options that the two vendors share.