Backward Compatibility Details (Level 2) consists of important changes in Microsoft® SQL Server™ version 7.0 that produce different behavior from earlier versions of SQL Server. For example, items in this category are those that have changed behavior in data type conversion or usage of selected functions, changed behavior of clauses in selected Transact-SQL statements and stored procedures, changed column names in selected system tables, and changed behavior due to the database compatibility setting. This topic covers backward compatibility details for these items.
| This subheading | Relates to |
|---|---|
| Backup and Restore | BACKUP CREATE DATABASE ALTER DATABASE RESTORE sp_dboption |
| Bulk Copy | bcp Utility |
| Configuration Options | Setting Configuration Options sp_configure (open objects and user connections options) |
| Database Pages and Extents | Pages and Extents |
| Data Types | CAST and CONVERT Data Types |
| DB-Library | dbcursorfetchex Trace Flags |
| Empty Strings | sp_dbcmptlevel CHARINDEX DATALENGTH LEFT LTRIM PATINDEX REPLICATE RIGHT RTRIM SPACE SUBSTRING UPDATETEXT |
| Indexes | CREATE INDEX |
| INSERT | sp_dbcmptlevel INSERT |
| Keyset Cursors | Keyset cursors |
| LTRIM and RTRIM Trimming Functions | SET CURSOR_CLOSE_ON_COMMIT ROLLBACK DECLARE CURSOR |
| OBJECT_ID | OBJECT_ID |
| ODBC | SQLGetDiagRec SQLMoreResults |
| RIGHT | Using Identifiers Reserved Keywords |
| Security | GRANT REVOKE DENY sp_addlinkedsrvlogin |
| SELECT | SELECT |
| SET SHOWPLAN | SET SHOWPLAN_ALL SET SHOWPLAN_TEXT |
| System Tables | Information Schema Views System Stored Procedures (Catalog Procedures) sysdatabases (logptr and dumptrdate columns) sysmessages (langid column) syslogins (language column) Computed Columns |
| Table Hints | DELETE FROM INSERT SELECT UPDATE |
| Transactions | SET TRANSACTION ISOLATION LEVEL |
| Triggers and System Stored Procedures | sp_dbcmptlevel sp_dboption sp_create_removable CREATE TRIGGER SET QUOTED_IDENTIFIER SET ANSI_NULLS SET ANSI_DEFAULTS |
| UPDATE | @@ERROR UPDATE INSERT |
| UPDATETEXT | UPDATETEXT WRITETEXT |
| Views | DELETE INSERT UPDATE |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| Using the SKIP and INIT clauses of the DUMP statement together overwrote the contents of the backup device unconditionally. | The SKIP and INIT clauses of the BACKUP statement preserve the Microsoft Tape Format media header. In some situations, this prevents overwriting the backup contents. The FORMAT clause overwrites the media unconditionally, generating a new header, and is required for media used for the first time or when necessary to overwrite the media header. | Expect different results as compared to earlier versions of SQL Server. If the media is empty, SKIP and INIT act the same as the FORMAT clause of the BACKUP or DUMP statements and write a new media header. If the media is not empty, SKIP and INIT do not write a new media header. |
| The LOAD statement did not create the database automatically when restoring the database backup. | It is no longer necessary to create the database before restoring it. The RESTORE statement re-creates the database automatically, including all files. However, database devices are not re-created in sysdevices. These devices are supported only for backward compatibility. After restoration, databases originally created using devices (DISK INIT) appear as if they had been created using SQL Server 7.0 file syntax. | Expect different results as compared to earlier versions of SQL Server. Consider using the new syntax in CREATE DATABASE and ALTER DATABASE for specifying files. |
| The NO_LOG clause of DUMP was used only when you ran out of space in the database and could not use DUMP TRANSACTION WITH TRUNCATE_ONLY to purge the log. The NO_LOG clause removes the inactive part of the log without making a backup copy of it, and saves space by not logging the operation. The TRUNCATE_ONLY clause of the DUMP statement removed the inactive part of the log without making a backup copy of it. | The NO_LOG and TRUNCATE_ONLY clauses of RESTORE are synonyms. Both clauses of BACKUP now remove the inactive part of the log without making a backup copy of it and truncates the log. | Expect different results as compared to earlier versions of SQL Server. Expect the NO_LOG and TRUNCATE_ONLY clauses of the BACKUP or DUMP statements to behave identically. |
| Recovery of multiple transaction logs could be performed without special keywords in the LOAD statement. | It is no longer possible to restore multiple transaction logs without using the WITH clauses of the RESTORE statement. | Expect different results as compared to earlier versions of SQL Server. Use the appropriate RESTORE syntax for restoring a database with multiple transaction logs as shown in the following examples. All but the last RESTORE statement should specify the NORECOVERY clause. |
| When loading a database, all database options of sp_dboption were unaffected and had to be set manually. | Changes to all sp_dboption database settings (except the offline, merge publish, published, and subscribed settings) are logged, like any other change. When a database is restored and recovered, all database options of sp_dboption are rolled forward. Every database option will be in its expected state at the time when recovery finished, consistent with the remainder of the database. | Expect different results as compared to earlier versions of SQL Server. It is no longer necessary to reset the database options after a RESTORE operation. |
A.Restore a database by applying a full database backup and multiple transaction logs
This example restores a database with multiple transaction log backups.
RESTORE DATABASE mydb
FROM mydb
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log1
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| A warm standby server could be brought up in read-only mode between recovery of each transaction log, provided that the no chkpt. on recovery option of sp_dboption was enabled. | A warm standby server can be brought up in read-only mode between transaction log restore operations if an undo file is used. |
Expect different results as compared to earlier versions of SQL Server. Use an undo file for a warm standby server using the STANDBY clause of RESTORE, as shown in the following example. |
B.Restore a database using the STANDBY clause and an undo file
This example brings the server up to allow write operations on the database(s) by using a final, necessary RESTORE statement.
RESTORE DATABASE mydatabase
FROM mydb_backup
WITH NORECOVERY
RESTORE LOG mydb
FROM mydb_log1
WITH RECOVERY STANDBY (FILENAME = 'c:\mssql7\data\mydbundo.dat')
RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY STANDBY (FILENAME = 'c:\mssql7\data\mydbundo.dat')
RESTORE DATABASE mydb
WITH RECOVERY
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| The bcp utility (using DB-Library) could import datetime or smalldatetime values in character-mode data files using:
· The default format used by DB-Library (mmm dd yyy hh:mmXX where XX is either A.M. or P.M.). · Any format supported by dbconvert except the ODBC format. bcp exported character-mode data files with datetime and smalldatetime values by using the default DB-Library format. |
The bcp utility (which uses ODBC) can import datetime and smalldatetime values in character-mode data files using:
· The default format used by DB-Library. · The format used by ODBC (yyyy-mm-dd hh:mm:ss[.f...]). However, bcp does not use other formats supported by dbconvert. bcp exports datetime and smalldatetime values using the ODBC default format. |
Expect different results as compared to earlier versions of SQL Server. To bulk copy data in character mode between SQL Server 6.x and SQL Server 7.0 servers, use the same bcp version (either SQL Server 6.x or SQL Server 7.0) for both importing and exporting data.
To export data from a SQL Server 7.0 server into a character-mode data file and later import that data using a DB-Library bulk copy application, use the SQL Server 6.x version of bcp. For existing datetime or smalldatetime values in a character-mode data file in a format other than the DB-Library default: · Change the values to the DB-Library default format for continued use with SQL Server 6.x and SQL Server 7.0 bcp. · Change the values to the ODBC format for exclusive use with SQL Server 7.0 bcp. |
| bcp exported money values in character mode data files using digit grouping symbols (for example, the comma in the United States when using the U.S. version of SQL Server, the US version of Microsoft Windows NT, and US settings) and two digits after the decimal point. | bcp exports money values in character mode data files without digit grouping symbols, but with four digits after the decimal point. | Expect different results as compared to earlier versions of SQL Server.
To read character files created by version 6.x DB-Library bcp in SQL Server 7.0, use the -6 switch. For more information, see bcp Utility. |
Administrative scripts may have used these configuration options. For more information about configuration options, see sp_configure and Setting Configuration Options.
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| open objects set the maximum number of database objects that can be open at one time on SQL Server. | Now an advanced option. Default value of 0 indicates automatic growth. | Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to open objects. For more information, see open objects Option. |
| user connections set the maximum allowed number of simultaneous connections to SQL Server. | Now an advanced option. Default value of 0 indicates automatic growth. | Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to user connections Option. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| A database extent consisted of eight 2 KB pages. | A database extent consists of eight 8 KB pages. Different objects can now share an extent or an object can have its own extent(s). A table and index both have a minimum of two pages. | Expect different results as compared to earlier versions of SQL Server. Adjust disk space requirements for adequate database storage. For more information, see Pages and Extents. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| Conversion of binary or varbinary to decimal or numeric was explicit. | This conversion is implicit. | Expect different results as compared to earlier versions of SQL Server. Expect conversions of binary or varbinary to decimal or numeric to be implicit. For more information about data type conversions, see CAST and CONVERT. For more information about system-supplied data types, see Data Types. |
| Conversion of binary or varbinary to smallmoney was not allowed. | This conversion is allowed. | Expect different results as compared to earlier versions of SQL Server. Use as appropriate. |
| Conversion of datetime or smalldatetime to decimal, numeric, float, real, int, smallint, tinyint, money, smallmoney, or bit was not allowed. | This conversion is allowed. | Expect different results as compared to earlier versions of SQL Server. Use as appropriate. |
| Conversion of float or real to binary or varbinary was not allowed. | This conversion is allowed. | Expect different results as compared to earlier versions of SQL Server. Use as appropriate. |
| Conversion of money or smallmoney to char or varchar was implicit. | This conversion is explicit. | Expect different results as compared to earlier versions of SQL Server. Expect conversions of money or smallmoney to char or varchar to be explicit. |
| Conversion of bit to money or smallmoney was not allowed. | This conversion is allowed. | Expect different results as compared to earlier versions of SQL Server. Use as appropriate. |
| Conversion of text to image was not allowed. | This conversion is allowed. | Expect different results as compared to earlier versions of SQL Server. Use as appropriate. |
| sysname data type was varchar(30). | sysname data type is nvarchar(128), which allows for 128 Unicode characters. | Expect different results as compared to earlier versions of SQL Server. Expect any columns or local variables defined as sysname to allow Unicode data. For more information about sysname see Data Types. |
| No direct support for the nchar, nvarchar, and ntext Unicode data types because maximum storage was 255 bytes for char, binary, varchar, and varbinary data types. | The maximum number of bytes that can be stored in char, binary, varchar, and varbinary data types is increased to 8,000. SQL Server 7.0 clients fully support the nchar, nvarchar, and ntext data types. SQL Server 6.x clients accessing a version 7.0 with these Unicode data types will experience these results: nvarchar data is returned as varchar and nchar data is returned as char. nvarchar and nchar values longer than 255 double-byte characters are truncated to 255 single-byte characters. Attempting to access ntext data causes SQL Server to issue a 4004 error. ntext data cannot be sent to version 6.x clients. char, varchar, binary, and varbinary values longer than 255 bytes are truncated to 255 bytes. |
Expect different results as compared to earlier versions of SQL Server. Expect changes in data when accessing version 7.0 data from version 6.x clients. To eliminate these differences, upgrade the clients to version 7.0 client software. |
| Using CONVERT to convert an empty string to int (CONVERT(int, "") or float (CONVERT(int, "") returned a zero. | Using CAST or CONVERT to convert an empty string to int (CAST(““ AS int) or float (CAST(““ AS int) returns an error message. | Expect different results as compared to earlier versions of SQL Server. Expect different results when converting an empty string to int or float, compared to pre-SQL Server 7.0. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| When connected to a version 6.x SQL Server, a call to dbcursorfetchex resulting in a cursor position after the end of the cursor result set returned:
· FAIL, with either a keyset or an insensitive cursor. · SUCCEED, with all row status indicators set to 0, with either a dynamic or forward cursor. |
A call to dbcursorfetchex resulting in a cursor position after the end of the cursor result set returns SUCCEED. All row status indicators are set to 0. This behavior applies to all types of cursors. | Expect different results as compared to earlier versions of SQL Server. Expect different results, compared to SQL Server 6.x, when a call to dbcursorfetchex results in a cursor position after the end of the cursor result set. To achieve SQL Server 6.x behavior, use trace flag 7505. For more information, see Trace Flags. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| An empty string could be interpreted as either a NULL or a single blank character. | Interpretation of an empty string is controlled by the setting of sp_dbcmptlevel. If the setting of sp_dbcmptlevel is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the setting of sp_dbcmptlevel is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel. | Expect different results as compared to earlier versions of SQL Server. Expect differences in interpretation of empty strings compared to earlier versions of SQL Server. Transact-SQL functions and statements affected by the setting of sp_dbcmptlevel include CHARINDEX, DATALENGTH, LEFT, LTRIM, PATINDEX, REPLICATE, RIGHT, RTRIM, SPACE, SUBSTRING, and UPDATETEXT. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| The DROP INDEX statement dropped the pages holding the clustered index B-tree when used on a clustered index. | On a clustered index, the DROP INDEX statement must rebuild all nonclustered indexes. SQL Server must also replace the clustered index keys in the nonclustered leaf rows with row pointers. | Expect different results as compared to earlier versions of SQL Server. Use the DROP_EXISTING clause of the CREATE INDEX statement if, for example, dropping or re-creating clustered indexes to set a new fill factor value. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| An INSERT x SELECT INTO Y statement ignored table Y and inserted the SELECT results into table X, as shown.
|
The INSERT...SELECT INTO syntax is retained only when the compatibility setting is equal to 60 or 65. If the compatibility setting is 70 and a similar query is executed, SQL Server returns a syntax error. | Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for queries using INSERT ...SELECT INTO syntax. Otherwise, use a compatibility setting of 70. |
| In an INSERT statement, a SELECT statement returning a scalar value was allowed in the VALUES clause. | The INSERT statement cannot have a SELECT statement in the VALUES clause as one of the values to be inserted. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65. | Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for using a SELECT statement in the VALUES clause of an INSERT statement. Otherwise, use a compatibility setting of 70. |
| A ROLLBACK statement in a stored procedure referenced in an INSERT table EXEC procedure statement caused the INSERT to be rolled back, but the batch continued. | A ROLLBACK statement in the stored procedure referenced by an INSERT...EXEC statement causes the entire transaction to be rolled back and the batch stops executing. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65. | Expect different results as compared to earlier versions of SQL Server. Set the compatibility setting to 60 or 65 by executing sp_dbcmptlevel to retain version 6.x functionality for ROLLBACK statement behavior inside an INSERT...EXEC statement. Otherwise, use a compatibility setting of 70. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| When using a keyset cursor, a row deletion followed by a row insertion using the same key as the deleted row caused the inserted row to occupy the slot of the original row. | When using a keyset cursor, a row deletion followed by a row insertion with the same key as the deleted row allows the original row to remain empty and the newly inserted row to be inserted at the end. | Expect different results as compared to earlier versions of SQL Server. Expect a change in behavior when inserting and deleting rows with the same key values when using keyset cursors. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| The LTRIM and RTRIM functions returned NULL in queries using zero-length strings:
|
Zero-length strings are supported. The queries shown return nonnull values; the first returns ““ and the second returns 0. | Expect different results as compared to earlier versions of SQL Server. LTRIM and RTRIM provide different output from earlier versions of SQL Server. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| A temporary table name in the OBJECT_ID function could be specified without the database name preceding the temporary table name. For example:
|
The database name must be specified before the temporary table name in the OBJECT_ID function. For example:
|
Expect different results as compared to earlier versions of SQL Server. Specify the database name before the temporary table name in the OBJECT_ID function. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| SQL_ERROR was returned by SQLExecute, SQLExecDirect, or SQLParamData when extended stored procedures or batches met the following criteria:
· The first data-returning statement caused an error (either by a run-time error or a RAISERROR statement with severity greater than or equal to 11). · There was data from any other statement, even a simple RETURN statement, after the error-causing statement. Due to the SQL_ERROR return code, the statement handle was available for use immediately. |
SQL_SUCCESS_WITH _INFO is returned when an ODBC 3.x application uses the ODBC SQL Server 3.5 driver included with this release (using SQLExecute, SQLExecDirect, or SQLParamData). Due to the SQL_SUCCESS |
Expect different results as compared to earlier versions of SQL Server. Handle SQL_SUCCESS _WITH_INFO using SQLGetDiagRec, and then call SQLMoreResults to process the remaining results, as appropriate. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| RIGHT was a reserved word. | RIGHT is a reserved keyword and should not be used for database object names (unless using identifiers). For more information about SQL Server 7.0 reserved keywords, see Using Reserved Keywords. | Expect different results as compared to earlier versions of SQL Server because RIGHT is now a reserved keyword. For more information about using RIGHT with identifiers, see Using Identifiers. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| The GRANT and REVOKE statements granted and revoked permissions, respectively. The REVOKE statement denied a permission to a single user that was granted to the user’s group. | The security model uses DENY in addition to GRANT and REVOKE. REVOKE has changed to remove a previously granted or denied permission. DENY creates an entry in the security system that denies a permission from a security account and prevents the user, group, or role from inheriting the permission through its group and role memberships. The REVOKE statement can no longer be used to deny permission to a user whose group has permission. Use the DENY statement to deny permissions explicitly to a specific user or group. | Expect different results as compared to earlier versions of SQL Server. Recognize that scripts using the pre-SQL Server 7.0 security model using GRANT and REVOKE behave differently than scripts using the current model of GRANT, REVOKE, and DENY if REVOKE was used to deny permissions to selected members of a group. |
| When executing an RPC, logins using integrated security mode referred to an internal login name with the backslashes (\) translated to underscores (_). For example, \Domain\Joe was translated to Domain_Joe. | Those servers upgraded to SQL Server that execute RPC calls no longer translate backslashes to underscores when using Windows NT Authentication. To use the SQL Server version 6.x naming convention for login names, use sp_addlinkedsrvlogin to map the backslash version of the username to an underscore version. | Expect different results as compared to earlier versions of SQL Server. Add references for sp_addlinkedsrvlogin to translate backslash version login names to underscore versions to maintain version 6.x login translations when the sending server of an RPC uses SQL Server version 7.0. |
A.Map specific backslash login to underscore login
This example maps the \LONDON1\nancyd login name to LONDON1_nancyd:
sp_addlinkedsrvlogin 'receiving_server_name',
false,
'LONDON1\nancyd',
'LONDON1_nancyd', NULL
B.Map specific backslash login to sa login
This example maps Nancy's LONDON1 login to the sa login, because Nancy’s domain login is part of the built-in administrators group:
sp_addlinkedsrvlogin 'receiving_server_name',
false,
'LONDON1\nancyd',
'sa', NULL
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| A SELECT statement without an ORDER BY clause returned the rows in an apparent ordered set. | An explicit ORDER BY clause for a SELECT statement is required to ensure any useful ordering of data. | Expect different results as compared to earlier versions of SQL Server. Add an explicit ORDER BY clause to all SELECT statements needing to produce ordered rows. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| When SET SHOWPLAN was set ON, SQL Server executed Transact-SQL statements. | When set ON, the SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements, which replace SET SHOWPLAN, do not execute Transact-SQL statements. | Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT are set to ON. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| System tables were used internally by SQL Server for a wide range of uses. | Some system tables have had minor changes, while others have been replaced by Information Schema Views that provide the same information. | Expect different results as compared to earlier versions of SQL Server. Use the provided Information Schema Views or ODBC catalog system stored procedures to obtain system catalog information. |
| The logptr column of sysdatabases was a pointer to the transaction log. | The logptr column has been renamed to status2. | Expect different results as compared to earlier versions of SQL Server. Remove all references of the logptr column of sysdatabases and replace with references to the status2 column. |
| The dumptrdate column of sysdatabases was the date of the last DUMP TRANSACTION. | This column is now Reserved. | Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to the dumptrdate column of sysdatabases |
| The langid column of sysmessages contained the SQL Server message group ID. | The langid column has been renamed to msglangid. | Expect different results as compared to earlier versions of SQL Server. Remove all references of the langid column of sysmessages and replace with references to the msglangid column. |
| A NULL value for the language column of the syslogins table was equivalent to specifying us_english. | A NULL value for the language column is no longer equivalent to us_english. | Expect different results as compared to earlier versions of SQL Server. Remove all NULL values for the language column of syslogins and replace with the name of the language to be used. |
| System tables obtained their column values by insertion of a specific value (SQL Server version 7.0 uses computed columns in many system and user-defined tables). | System tables (and user-defined tables) can now use computed columns. | Expect different results as compared to earlier versions of SQL Server. SQL Server version 6.5 queries involving table hints and system tables may still produce the same result set, but may behave differently in SQL Server version 7.0. For example, the query may still wait for some locks even if the NOLOCK table hint has been specified in the query's FROM clause. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| These table hints (previously called optimizer hints) could be specified as just the keyword following the FROM clause: FASTFIRSTROW, HOLDLOCK, INDEX, NOLOCK, PAGLOCK, TABLOCK, TABLOCKX, and UPDLOCK. |
Table hints must be specified following the FROM clause using a WITH clause. Only the HOLDLOCK table hint can be specified with or without parentheses. All other table hints require parentheses around them. | Expect different results as compared to earlier versions of SQL Server. Put parentheses around all table hints. For more information, see DELETE, FROM, INSERT, SELECT, and UPDATE. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| When CURSOR_CLOSE_ON_COMMIT was set OFF, a ROLLBACK statement did not close a Transact-SQL cursor defined with the DECLARE CURSOR statement. Server cursors opened through database API functions were also left open after a ROLLBACK statement. | When CURSOR_CLOSE_ON_COMMIT is set OFF, a ROLLBACK statement closes any Transact-SQL cursor defined with the SQL-92 form of the DECLARE CURSOR statement, unless the DECLARE CURSOR statement contains either the INSENSITIVE or STATIC keywords. All API server cursors are also closed unless they have been defined as STATIC cursors (such as using the ODBC SQL_CURSOR_STATIC attribute). | Expect different results as compared to earlier versions of SQL Server. Reopen all cursors after issuing a ROLLBACK statement. |
| The REPEATABLE READ clause of the SET TRANSACTION ISOLATION LEVEL statement behaved identically to the SERIALIZABLE clause. There was no way to ensure repeatable reads without also protecting against phantoms (after a rollback, the value read logically never existed.) Transactions that required REPEATABLE READ semantics had to pay the additional concurrency penalty of serializability. | The REPEATABLE READ clause now does not necessarily protect against phantoms. Serializable transactions, set using the SERIALIZABLE clause of SET TRANSACTION ISOLATION LEVEL, allow less concurrency than the REPEATABLE READ clause because they protect against phantoms. | Expect different results as compared to earlier versions of SQL Server. Many applications only need REPEATABLE READ semantics for correct operation. Use the REPEATABLE READ clause of SET TRANSACTION ISOLATION LEVEL for applications requiring REPEATABLE READ semantics but that do not need phantom protection. If phantom protection is required, use the SERIALIZABLE clause. |
Here is a summary of phantom protection for both SQL Server versions 6.5 and 7.0 using SET TRANSACTION ISLATION LEVEL.
| Phantom protection | SQL Server 6.5 | SQL Server 7.0 |
|---|---|---|
| REPEATABLE READ | Yes | No |
| SERIALIZABLE | Yes | Yes |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| sp_helpsql provided syntax for Transact-SQL statements, system stored procedures, and other special topics. | sp_helpsql is included, but no longer returns syntax information for Transact-SQL statements or system stored procedures. Executing sp_helpsql produces a message that recommends obtaining syntax information from SQL Server Books Online. | Expect different results as compared to earlier versions of SQL Server. Use SQL Server Books Online for the syntax of Transact-SQL statements and system stored procedures. |
| Only one trigger for each data modification event (INSERT, UPDATE, DELETE) was allowed for each table. If a new trigger was created for a specific data modification event, it replaced the previous trigger. | Microsoft® SQL Server™ allows multiple triggers to be created for each data modification event (DELETE, INSERT, or UPDATE). For example, if CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE trigger, an additional update trigger is created. | Expect different results as compared to earlier versions of SQL Server. Enable multiple triggers by setting the compatibility level to 70 for sp_dbcmptlevel. Retain pre-SQL Server 7.0 behavior by setting the compatibility level to 60 or 65. For more information, see sp_ dbcmptlevel and CREATE TRIGGER. |
| If a trigger modified the table on which it was defined, the triggers were not invoked recursively for that modification. | SQL Server allows recursive invocation of triggers. | Expect different results as compared to earlier versions of SQL Server. Enable recursive triggers by setting the recursive triggers setting of sp_dboption. |
| Several parameters of sp_create_removable referred to devices. | Devices have been replaced with files and filegroups. | Expect different results as compared to earlier versions of SQL Server. Replace all device references in sp_create_removable with references to filegroups sp_create_removable. |
| References to text or image columns in either the inserted or deleted tables appeared as NULL. | References to text or image columns in both the inserted and deleted tables are no longer allowed unless the compatibility level setting of sp_dbcmptlevel is 60 or 65. | Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when referring to text or image columns in inserted and deleted tables when using CREATE TRIGGER, depending on the setting of sp_dbcmptlevel. |
| SQL Server searched the current database followed by a search in master for a stored procedure using the sp_ prefix. | Stored procedures with the prefix sp_ are first looked up in master. If a user-defined stored procedure has the same name as a system-supplied stored procedure residing in master, SQL Server always finds the system-supplied stored procedure. | Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when calling user-defined stored procedures with the sp_ prefix. Either explicitly qualify the name of the user-defined stored procedure, or rename the user-defined stored procedure. |
| The settings of SET ANSI_NULLS and SET QUOTED_IDENTIFIER statements were active only during the session that changed either option. | The settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS are saved when a stored procedure is created or altered. These original settings are enabled when the stored procedure is executed, and any client session settings are restored afterward. Within the stored procedure, any changes to SET ANSI_NULLS do not take effect until after the stored procedure executes. | Expect different results as compared to earlier versions of SQL Server. Develop databases or applications with one setting for SET QUOTED_IDENTIFIER, SET ANSI_NULLS, and all other pertinent SET options. If a client session changes SET options, do so outside of stored procedures. |
| When executing remote stored procedures, these procedures may have assumed non-standard behavior for the options set by SET ANSI_DEFAULTS. In addition, remote stored procedures may not have explicitly set these options (set by SET ANSI_DEFAULTS) to OFF. | When executing remote stored procedures, these procedures are executed with SET ANSI_DEFAULTS set to ON. | Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when executing remote stored procedures if non-standard settings were used with SET ANSI_DEFAULTS, or if options were not explicitly set OFF. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| An UPDATE statement encountering an arithmetic overflow condition would set the updated value to NULL, or skip the update if the value belonged to a nonnull column. | SQL Server terminates the UPDATE or INSERT statement, even for one row, if arithmetic overflow occurs. No rows are updated. | Expect different results as compared to earlier versions of SQL Server. Use the @@ERROR function to test for errors after UPDATE or INSERT statements. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| UPDATETEXT initialized text columns to NULL, allocating a full 2K page. | If the compatibility level setting of sp_dbcmptlevel is 65, UPDATETEXT initializes text columns to NULL. However, if the compatibility level setting of sp_dbcmptlevel is 70, WRITETEXT initializes text columns to NULL; UPDATETEXT initializes text columns to an empty string. | Expect different results as compared to earlier versions of SQL Server. Expect differences in behavior when initializing text values to NULL (using UPDATETEXT or WRITETEXT) depending on the compatibility level setting of sp_dbcmptlevel. |
| Pre-SQL Server 7.0 | SQL Server 7.0 | Recommendations |
|---|---|---|
| Updatable views were restricted to modifications that affected only one table. | Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user’s update request unambiguously to updates in the base tables referenced in the view’s definition. | Expect different results as compared to earlier versions of SQL Server. Expect differences in behavior when working with updatable views with more than one table involved in the DELETE, INSERT, or UPDATE statements. |