Chapter 4 - Backward Compatibility

1

Administrative statements, stored procedures, or SQL Server items that have been removed from, or are no longer supported in, SQL Server 7.0. Administrative tools or scripts using these items must be fixed prior to using SQL Server 7.0.

3

Items supported for backward compatibility only. Any item included in this category is fully supported, but may be removed or unsupported in a future release. SQL Server 7.0 provides features that accomplish these tasks more efficiently and have ongoing support.

Backup and Restore

Upgrading from an Earlier Version of SQL Server
BACKUP
RESTORE
DUMP
LOAD
sysbackuphistory
sysbackupdetail
sysrestorehistory
sysrestoredetail
backupfile
backupmediafamily
backupmediaset
backupset
restorefile
restorefilegroup
restorehistory

Custom Sort Orders

Character sets, sort orders, and Unicode collations

Database Options

sp_dboption (subscribe and no chkpt. on recovery options)
sp_addsubscription
RESTORE

DBCC

DBCC DBREPAIR
DBCC DBREINDEX
DROP DATABASE
DBCC MEMUSAGE
DBCC SHRINKDB
DBCC SHRINKDATABASE

DECnet Network Library

DECnet Sockets Net-Library

Disk Mirroring

DISK MIRROR
DISK REMIRROR
DISK UNMIRROR

Open Data Services

Windows NT Component Services
SRV_CONFIG
SRV_PROC
SRV_SERVER
srv.h
Opends60.lib

Replication

Restricted publications
DBOption object
ReplicationDatabase object EnablePublishing property
repl_publisher login

Segments

CREATE INDEX
CREATE TABLE
sp_addsegment
sp_dropsegment
sp_extendsegment
sp_helpsegment
CREATE DATABASE
ALTER DATABASE

SET DISABLE_DEF_CNST_CHK

SET DISABLE_DEF_CNST_CHK

SQL Alerter

SQLALRTR.exe

System Stored Procedures (Replication)

sp_replica
sp_replsync
sp_helppublicationsync
sp_subscribe
sp_unsubscribe
@@ERROR
sp_changepublication
sp_addpublisher
sp_adddistpublisher
sp_droppublisher
sp_distcounters

System Stored Procedures (Tasks)

sp_addalert
sp_addnotification
sp_addoperator
sp_dropalert
sp_dropnotification
sp_dropoperator
sp_helpalert
sp_helphistory
sp_helpnotification
sp_helpoperator
sp_purgehistory
sp_runtask
sp_stoptask
sp_updatealert
sp_updatenotification
sp_updateoperator
sp_add_alert
sp_add_notification
sp_add_operator
sp_delete_alert
sp_delete_notification
sp_delete_operator
sp_help_alert
sp_help_jobhistory
sp_help_notification
sp_help_operator
sp_purge_jobhistory
sp_start_job
sp_stop_job
sp_update_alert
sp_update_notification
sp_update_operator

Transactions

Data type conversions

The VOLUME clause of the DUMP and LOAD statements indicated the volume ID for a dump device.

The VOLUME keyword has been replaced by the MEDIANAME clause. Use of the VOLUME clause results in an error.

Remove all references of the VOLUME keyword in all BACKUP, DUMP, LOAD, or RESTORE statements and replace with references to MEDIANAME.

The sysbackuphistory, sysbackupdetail, sysrestorehistory, and sysrestoredetail system tables tracked DUMP and LOAD history information.

The DUMP and LOAD history tracking system tables have been removed and replaced by a new set of system tables.

Remove all references to sysbackuphistory, sysbackupdetail, sysrestorehistory, and sysrestoredetail. Because the structure and contents of the backup system tables have changed significantly, familiarize yourself with these new system tables before referencing them: backupfile, backupmediafamily, backupmediaset, backupset, restorefile, restorefilegroup, and restorehistory.

backup buffer size specified the size of the dump and load buffer (used to increase backup speed).

Removed; no longer supported.

Remove or comment out all references to backup buffer size.

database size set the default number of megabytes (MB) allocated to each new user database.

Removed; no longer supported.

Remove or comment out all references to database size.

hash buckets set the number of buckets used for hashing pages to buffers in memory.

Removed; no longer supported.

Remove or comment out all references to hash buckets.

LE threshold minimum determined the minimum number of page locks required before escalating to a table lock.

Removed; no longer supported.

Remove or comment out all references to LE threshold minimum.

logwrite sleep specified the number of milliseconds that a write to the log will be delayed if the buffer is not full.

Removed; no longer supported.

Remove or comment out all references to logwrite sleep.

memory set the size of available memory, in 2K units.

Removed; no longer supported. Memory is configured automatically based on need and available memory. To control the range of memory configured automatically, use the min server memory and max server memory options. For more information about the min server memory and max server memory option see "Server Memory Options" in Microsoft SQL Server Administrator's Companion.

Remove or comment out all references to memory.

procedure cache specified the percentage of memory allocated to the procedure cache after the SQL Server memory needs are met.

Removed; no longer supported.

Remove or comment out all references to procedure cache.

RA cache miss limit specified the number of cache misses that occurred during a horizontal traversal before read-ahead started for that command.

Removed; no longer supported.

Remove or comment out all references to RA cache miss limit.

RA pre-fetches determined how far ahead the read-ahead manager read (on an extent basis) before the pre-fetch manager idled.

Removed; no longer supported.

Remove or comment out all references to RA pre-fetches.

RA worker threads specified the number of threads used to service read-ahead requests.

Removed; no longer supported.

Remove or comment out all references to RA worker threads.

recovery interval set the maximum number of minutes per database that SQL Server needed to complete its recovery procedures in case of a system failure.

Configured automatically by SQL Server.

Remove or comment out all references to recovery interval.

SMP concurrency specified the maximum number of CPUs that would be used by SQL Server.

Removed; no longer supported.

Remove or comment out all references to SMP concurrency.

tempdb in ram placed the tempdb database in RAM, if needed.

No longer supported because few situations experienced significant server performance improvement. SQL Server 7.0 has been optimized for maximum performance. This option may be available in a future release.

Remove or comment out all references to tempdb in ram.

user connections set the maximum number of simultaneous connections to SQL Server allowed.

Now an advanced option. Default value of 0 indicates automatic growth.

Expect different results when using compared to earlier versions of SQL Server. Remove or comment out all references to user connections.

Custom sort orders were installed from definition files (usually with an .srt file extension).

Removed; no longer available or supported.

Remove all references to custom sort orders. During SQL Server installation, select a supported character set, sort order, and Unicode collation combination. For more information, see "Character Set," "Sort Order," and "Unicode Collation" in this volume.

The ON database_device = size clause of ALTER DATABASE specified the amount of space, in MB, allocated to the database extension and could be used following DISK INIT to alter the database device size.

If the file was not created originally by DISK INIT, the ON database_device = size syntax cannot be specified with ALTER DATABASE. Instead, use the MODIFY FILE clause of ALTER DATABASE to alter the size of a database file.

Remove all references of the ON database_device = size clause of ALTER DATABASE and replace with references to the MODIFY FILE clause of ALTER DATABASE.

The subscribe option of sp_dboption enabled or disabled a database for subscriptions.

Removed; no longer available. Use sp_addsubscription to enable or disable a database for subscriptions.

Remove all references of the subscribe option of sp_dboption and replace with references to sp_addsubscription.

Version 3.x of the Data Access Objects (DAO) functioned properly when accessing SQL Server version 6.x servers.

Because the ODBC version 3.70 driver shipped with SQL Server 7.0 exposes new GUID and Unicode data types when connecting to SQL Server 7.0, DAO version 3.x does not work properly with SQL Server 7.0. However, SQL Server 7.0 does provide the odbccmpt utility, which can enable SQL Server version 6.x ODBC compatibility for a DAO application.

Use the \mssql7\binn\odbccmpt.exe utility. For more information, see "odbccmpt utility" in Microsoft SQL Server Building Applications.

DBCC DBREPAIR dropped the specified database.

Removed; no longer supported or available.

Remove all references of DBCC DBREPAIR and replace with references to DROP DATABASE.

DBCC SHRINKDB either returned the minimum size to which a database could shrink, or shrank the size of the specified database to the specified value.

Removed; no longer supported or available.

Remove all references of DBCC SHRINKDB and replace with references to DBCC SHRINKDATABASE. Consider shrinking databases automatically by using the autoshrink option of sp_dboption.

SQL Server: Buffer Manager Object

Procedure Cache Pages In Use

SQL Server: Cache Manager Object

Procedure Cache Hit Ratio

Procedure Cache Object Counts*

* These counters are available for various categories of cache objects including adhoc sql, prepared sql, procedures, triggers, and so on.

DB-Library's two-phase commit special library managed transactions distributed across two or more servers.

The DB-Library two-phase commit is no longer supported. Use Microsoft Distributed Transaction Coordinator (MS DTC) to accomplish simultaneous updates on two servers.

Remove all references to DB-Library's two-phase commit. Use MS DTC instead of the DB-Library two-phase commit.

For Intel-based, MIPS-based, and Alpha AXP-based computers, server DECnet Sockets Net-Libraries provided connectivity with PATHWORKS networks by allowing clients running on VMS to connect to SQL Server.

Removed; no longer supported.

Remove all references to the DECnet Sockets Net-Libraries.

DISK REINIT and DISK REFIT restored usage information from system tables when a device existed (the file was present) but the entries in sysusages no longer existed.

Removed; no longer supported or available.

Remove all references to DISK REINIT. Replace all references of DISK REFIT with references to ALTER DATABASE, which adds and drops filegroups included in a database, and modifies the size of each database filegroup.

DISK MIRROR, DISK REMIRROR, and DISK UNMIRROR performed SQL Server disk mirroring.

No longer supported because SQL Server mirroring is no longer supported.

Use Microsoft Windows NT or hardware-based RAID. For more information, see your Windows NT or hardware documentation.

The SORTED_DATA_REORG clause of CREATE INDEX eliminated the sort performed when a clustered index was created.

Replaced by the DROP_EXISTING clause of CREATE INDEX.

Remove all references to the SORTED_DATA_REORG clause of CREATE INDEX and replace with references to DROP_EXISTING.

bcp could import an already sorted data file into a SQL Server table. Creating a clustered index on an ordered table could be optimized by using the SORTED_DATA clause of CREATE INDEX. The SORTED_DATA clause forced SQL Server not to sort or reorganize the previously ordered table.

SQL Server returns an error message stating that the SORTED_DATA clause of CREATE INDEX is ignored and no longer supported.

Remove or comment out all references to the SORTED_DATA clause of CREATE INDEX. Consider creating the clustered index before using bcp to import the data. bcp uses improved index maintenance strategies to make data importation with a preexisting index faster than earlier releases and avoids resorting of data after importation.

The SORTED_DATA clause of CREATE INDEX eliminated the sort performed when a clustered index was created.

No longer supported. If the SORTED_DATA clause is specified, SQL Server returns an error message in addition to completing the CREATE INDEX statement successfully.

Expect different results as compared to earlier versions of SQL Server. Remove any references to the SORTED_DATA clause of CREATE INDEX.

The ODBC client driver for Open Data Services gateways (ODSGT32.DLL) and associated resource files were used by ODBC clients to connect to Open Data Services gateway servers.

Not shipped with SQL Server version 7.0. The SQL Server version 6.x ODSGT32.DLL and associated resource files work against an Open Data Services gateway recompiled with SQL Server version 7.0 headers and libraries.

Use the SQL Server version 6.x ODBC client driver for Open Data Services (ODSGT32.DLL) and associated resource files to connect from a ODBC client to an Open Data Services gateway.
Consider redesigning your application using Windows NT Component Services.

Earlier versions of SQL Server could make remote stored procedure calls against gateways compliant with 6.x and/or 4.x versions of Open Data Services.

SQL Server 7.0 does not support remote stored procedure calls against gateways compliant with 6.x and 4.x versions of Open Data Services. SQL Server 7.0 does support remote stored procedure calls against gateways compliant with 7.0

Gateways compiled and linked with earlier versions of Open Data Services should be recompiled with SQL Server 7.0 version of Open Data Services. Consider using distributed query if your target data source has an ODBC or an OLE DB provider on Windows NT or Windows 95/98.

ISQL_w

SQL Server Query Analyzer

SQL Client Configuration

Client Network Utility

SQL Help

N/A

SQL Trace

SQL Server Profiler

SQL Service Manager

SQL Server Service Manager

Restricted publications could be created through the user interface and used in replicating data.

Restricted publications cannot be created through the user interface and are no longer supported.

Remove all references to restricted publications. A replacement for restricted publications will be available in a later release. For more information, see "Overview of Replication" in Microsoft SQL Server Distributed Data Operations and Replication.

The repl_publisher login allowed replication processes on the distributor to connect to a subscription server and replicated table schema and data to destination databases.

No longer available.

Remove all references to the repl_publisher login and replace with references to a publication access list (PAL). For more information about PALs, see "Restricting Logins to a Publication" in Microsoft SQL Server Distributed Data Operations and Replication.

DENY was not a reserved keyword and could be used as an object identifier.

DENY is a reserved keyword. If DENY is used as an object identifier, all references to the object must use delimited identifiers.

Rename any object named DENY or [DENY]. Change all Transact-SQL statements and scripts referencing the object to use the new object name. If the object name is not changed, ensure that all Transact-SQL statements and scripts use delimited using identifiers.

Indexes could be placed on segments using the CREATE INDEX statement.

Segments are no longer supported. However, CREATE INDEX can create an index on a filegroup.

Remove all references of segments and replace with references to filegroups within a CREATE INDEX statement.

User-defined segments allowed the placement of database objects on certain devices for performance reasons.

Segments are no longer supported. Multidisk RAID devices generally provide a greater increase in performance with a lower associated administrative cost.

Use filegroups for user-defined PLACEMENT of data, indexes, or text. Remove all references to these segment-related system stored procedures:
sp_addsegment
sp_dropsegment
sp_extendsegment
sp_helpsegment
Create, modify, or drop files and filegroups; and place indexes on files or filegroups using CREATE TABLE, CREATE DATABASE, ALTER DATABASE, and CREATE INDEX.

SQL Executive provided the SQL Server scheduling engine. SQL Executive offered extensive and varied task scheduling and alerting abilities, and was capable of handling large client/server environments.

SQL Executive tasks are now performed by SQL Server Agent.

Use SQL Server Agent for scheduling purposes. For more information, see "Configuring the SQL Server Agent Service" in Microsoft SQL Server Administrator's Companion. 

The SET DISABLE_DEF_CNST_CHK setting controlled interim constraint checking.

Removed; no longer available.

Remove or comment out all references to SET DISABLE_DEF_CNST_CHK.

SET SHOWPLAN generated a description of the processing plan for the query and processed it immediately unless the SET NOEXEC setting was enabled.

SET SHOWPLAN has been replaced with SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT. The SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements return only query or statement execution plan information and do not execute the query or statement. To execute the query or statement, turn the appropriate showplan statement OFF. The query or statement will then execute.

Remove all references to either SET SHOWPLAN ON or SET SHOWPLAN OFF and replace with references to either SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET SHOWPLAN_ALL OFF, or SET SHOWPLAN_TEXT OFF. Expect differences in behavior as compared to earlier versions of SQL Server. Only when either SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT is OFF will the query or statement be executed.

SQL Alerter, SQLALRTR.exe, was used to integrate the alert engine with the Windows NT Performance Monitor alerter.

Removed; no longer supported or available. Replaced by SQL Server Performance Condition Alerts.

Remove all references to SQL Alerter and replace with references to SQL Server Performance Condition Alerts.

SQL-DMO is implemented in Sqlole.dll. The SQL-DMO objects exhibit properties, methods and events that automate administrative tasks for SQL Server version 6.5 and earlier. SQL-DMO, implemented in Sqlole.dll, cannot connect to and operate against SQL Server 7.0.

SQL-DMO is implemented in Sqldmo.dll. The SQL-DMO objects exhibit properties, methods and events that automate administrative tasks for SQL Server 7.0. SQL Server 7.0 SQL-DMO cannot connect to and operate against a version 6.5 or earlier SQL Server.

Rewrite SQL-DMO applications.
If the application must operate against both SQL Server 7.0 and earlier versions of SQL Server, reference both Sqldmo.dll and Sqlole.dll components in the application. Develop new, separate subroutines referencing the SQL Server 7.0 SQL-DMO objects from the existing subroutines.
If the application will work against SQL Server 7.0 only, rewrite existing subroutines to reference SQL Server 7.0 SQL-DMO objects.
If the application will not be used against your new SQL Server 7.0 server(s), continue to use the application unchanged.

xp_snmp_getstate returned the state of the SQL Server Simple Network Management Protocol (SNMP) agent. xp_snmp_raisetrap permitted a client to define and send a trap (an SNMP alert) to an SNMP client.

Removed; no longer available.

Remove or comment out all references to either xp_snmp_getstate or xp_snmp_raisetrap.

sp_replica remotely set (on a Subscriber) a sysobjects category bit that marked the table as a replica.

Removed; no longer supported or available.

Remove or comment out all references to sp_replica.

sp_helppublicationsync provided information about a scheduled synchronization task for a publication.

No longer supported. An error message is returned if this stored procedure is used in SQL Server version 7.0.

Remove or comment out all references to sp_helppublicationsync.

name value of the value parameter in sp_changepublication was used to provide the new publication name.

Removed; no longer supported or available.

Remove all references to the name value parameter of sp_changepublication.

sp_droppublisher dropped a publication server.

Removed; no longer supported or available.

Remove all references to sp_droppublisher. To drop a Publisher at a Distributor, use sp_dropdistpublisher.

Several system stored procedures were used for documenting keys.

Removed; no longer supported or available.

Use declarative referential integrity by implementing keys and constraints with either ALTER TABLE or CREATE TABLE. Remove all references to these system stored procedures and replace with references to either sp_help or sp_helpconstraint:
sp_commonkey
sp_dropkey
sp_foreignkey
sp_helpjoins
sp_helpkey
sp_primarykey

sp_dbinstall installed a database and its devices, and was used for removable media.

Removed; no longer supported or available.

Remove or comment out all references to sp_dbinstall and replace with references to sp_attach_db.

sp_makestartup and sp_unmakestartup set a stored procedure for auto execution and discontinued auto execution of the stored procedure, respectively.

Removed; no longer supported or available.

Remove all references of either sp_makestartup or sp_unmakestartup and replace with references to sp_procoption.

The fallback setting of sp_serveroption indicated a fallback server.

The fallback option of sp_serveroption is no longer available because fallback support is no longer supported using the fallback system stored procedures.

Remove or comment out all references to the fallback option of sp_serveroption.

sp_droplanguage dropped an alternate language from the server and removed its row from master.dbo.syslogins.

Removed; no longer supported.

Remove or comment out all references to sp_droplanguage.

sp_devoption displayed or set device status.

Removed; no longer available.

Remove or comment out all references to sp_devoption.

sp_helplog reported the name of the device that contains the first page of the log in the current database.

Removed; no longer available.

Remove or comment out all references to sp_helplog.

sp_sqlexec provided a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application.

Removed; no longer available.

Remove or comment out all references to sp_sqlexec.

sp_addlanguage added an alternate language to a server.

Removed; no longer available.

Remove or comment out all references to sp_addlanguage.

sp_addalert

sp_add_alert

sp_addoperator

sp_add_operator

sp_dropnotification

sp_delete_notification

sp_helpalert

sp_help_alert

sp_helpnotification

sp_help_notification

sp_purgehistory

sp_purge_jobhistory

sp_stoptask

sp_stop_job

sp_updatenotification

sp_update_notification

System tables were used internally by SQL Server for a wide range of uses, including maintaining the list of character sets that SQL Server could use and containing information about active locks.

System tables have changed significantly. Most pre-SQL Server 7.0 system tables will continue to work properly. Views provided allow applications referencing SQL Server 6.x system tables to continue functioning properly. However, some SQL Server 7.0 data cannot be referenced through these views.

Use the provided Information Schema Views or ODBC catalog stored procedures to obtain system table information. Modify scripts as appropriate. Any scripts referencing pre-SQL Server 7.0 system tables will not be converted properly.

sysdevices contained one row for each disk dump, tape dump, and database device.

The mirrorname and stripeset columns have been removed. sysdevices is retained only for dump devices and also for backward compatibility (supporting DISK INIT and pre-SQL Server 7.0 CREATE DATABASE syntax).

Remove or comment out all references to the mirrorname and stripeset columns of sysdevices.

sysindexes contained one row for each clustered index and one row for each nonclustered index.

The distribution, segment, rowpage, keys1, and keys2 columns have been removed.

Remove or comment out all references to the distribution, segment, rowpage, keys1, and keys2 columns of sysindexes.

syslocks contained information about active locks.

Replaced by syslockinfo.

Remove or comment out all references to syslocks and replace with references to syslockinfo.

sysprocesses contained information about SQL Server processes.

The gid column has been removed.

Remove or comment out all references to the gid column of sysprocesses.

syssegments contained one row for each segment (named collection of disk fragments).

Removed; no replacement. Segments are no longer supported. Use filegroups instead.

Remove all references to syssegments. Use filegroups instead by using CREATE DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, and CREATE INDEX.

sysusages contained one row for each disk-allocation piece assigned to a database.

Removed; no replacement. SQL Server 7.0 relies on sysdevices for database file information. Filegroups are supported, and the sysfiles and sysfilegroups system tables are added. These system tables reside in every database and describe database files and filegroups.

Remove or comment out all references to sysusages.

A data type conversion error not inside a transaction returned an error to SQL Server. If the data type conversion was inside a transaction, the transaction continued. For example:
USE pubs
CREATE TABLE test (c1 int)
GO
BEGIN TRANSACTION
GO
INSERT INTO test VALUES (1)
GO
INSERT INTO test VALUES ('aaa')
GO
COMMIT TRANSACTION
GO
SELECT *
FROM test

SQL Server returns an error when an attempted data type conversion fails. If the data type conversion error occurs inside a transaction, the transaction is terminated.

Expect differences in behavior as compared to earlier versions of SQL Server if a data type conversion fails inside a transaction.

The probe login, which required no password, was used by DB-Library and Windows NT Performance Monitor. The DB-Library two-phase commit library used the probe login to check on the status of distributed transactions. It was also used by Windows NT Performance Monitor to get statistics from SQL Server.

The probe login has been eliminated. Windows NT Performance Monitor will always use Windows NT Authentication, known earlier as integrated security, to connect to SQL Server.

Ensure that your Windows NT username and password have the appropriate privileges to use Windows NT Performance Monitor.

Backup and Restore

BACKUP
CREATE DATABASE
ALTER DATABASE
RESTORE
sp_dboption

Configuration Options

Setting Configuration Options
sp_configure (open objects and user connections options)

Data Types

CAST and CONVERT
Data Types

Empty Strings

sp_dbcmptlevel 
CHARINDEX
DATALENGTH
LEFT
LTRIM
PATINDEX
REPLICATE
RIGHT
RTRIM
SPACE
SUBSTRING
UPDATETEXT

INSERT

sp_dbcmptlevel 
INSERT

LTRIM and RTRIM

SET CURSOR_CLOSE_ON_COMMIT
ROLLBACK
DECLARE CURSOR

ODBC

SQLGetDiagRec
SQLMoreResults

Security

GRANT
REVOKE
DENY
sp_addlinkedsrvlogin

SET SHOWPLAN

SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT

Table Hints

DELETE
FROM
INSERT
SELECT
UPDATE

Triggers and System Stored Procedures

sp_dbcmptlevel
sp_dboption
sp_create_removable
CREATE TRIGGER
SET QUOTED_IDENTIFIER
SET ANSI_NULLS
SET ANSI_DEFAULTS

UPDATETEXT

UPDATETEXT
WRITETEXT

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 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.

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 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.

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.

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" in Microsoft SQL Server Administrator's Companion.

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" in this volume.

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" in Microsoft SQL Server Transact-SQL and Utilities Reference. For more information about system-supplied data types, see "Data Types" in Microsoft SQL Server Administrator's Companion.

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 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 text to image was not allowed.

This conversion is allowed.

Expect different results as compared to earlier versions of SQL Server. Use as appropriate.

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.

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.

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" in Microsoft SQL Server Transact-SQL and Utilities Reference.

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" in Microsoft SQL Server Building Applications. 

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.

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.

An INSERT x  SELECT INTO Y statement ignored table Y and inserted the SELECT results into table X, as shown.
INSERT X
SELECT select_list INTO Y

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.

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.

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.

The LTRIM and RTRIM functions returned NULL in queries using zero-length strings:
SELECT RTRIM("")

SELECT DATALENGTH(RTRIM(""))

Zero-length strings are supported. The queries shown return non-null 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.

A temporary table name in the OBJECT_ID function could be specified without the database name preceding the temporary table name. For example:
CREATE TABLE #mytemptable
( c1 int, c2 char(2))

USE tempdb
SELECT OBJECT_ID('#mytemp
table')

The database name must be specified before the temporary table name in the OBJECT_ID function. For example:
SELECT OBJECT_ID('tempdb..#mytemptable')

 

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.

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_WITH_INFO return code, process the results for that statement handle before it is available for use.

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.

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 "Reserved Keywords" in Microsoft SQL Server Database Developer's Companion.

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" in Microsoft SQL Server Database Developer's Companion.

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.

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.

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.

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 stored procedures to obtain system catalog information.

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 

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.

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" in Microsoft SQL Server Transact-SQL and Utilities Reference.

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.

REPEATABLE READ

Yes

No

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.

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.

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.

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.

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 non-null 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.

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.

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.

Backup and Restore

BACKUP
RESTORE
CREATE DATABASE

DBCC

DBCC NEWALLOC
DBCC CHECKALLOC
DBCC ROWLOCK
Architecture Enhancements
DBCC TEXTALL
DBCC CHECKDB
DBCC TEXTALLOC
DBCC CHECKTABLE
DBCC DBREPAIR
DROP DATABASE

Open Data Services

Opends60.lib
srv_config
srv_config_alloc
SRV_GETCONFIG
srv_init
srv_run
SRV_TDSVERSION
srv_getuserdata
srv_setuserdata
srv_errhandle
SRV_IODEAD
srv_log
srv_sfield
srv_event
SRV_EVENTDATA
SRV_GETSERVER
SRV_GOT_ATTENTION
srv_handle
srv_pre_handle
srv_post_handle
srv_setevent
srv_terminatethread
SRV_ATTENTION
SRV_CONNECT
SRV_DISCONNECT
SRV_LANGUAGE
SRV_RPC
SRV_EXIT
SRV_START
SRV_SLEEP
SRV_RESTART
SRV_STOP
srv_langcpy
srv_langlen
srv_langptr
srv_paramdata
srv_paramlen
srv_parammaxlen
srv_paramname
srv_paramnnumber
srv_paramset
srv_paramstatus
srv_paramtype
srv_returnval
srv_rpcdb
srv_rpcnumber
srv_rpcoptions
srv_clearstatistics
srv_sendstatistics
srv_alloc
srv_bmove
srv_bzero
srv_free
srv.h
srv_describe
srv_setcollen
srv_setcoldata
srv_paramsetoutput
srv_paraminfo
srv_sendstatus

Security

GRANT
Authentication
SETUSER

SET SHOWPLAN

SET SHOWPLAN_TEXT
SET SHOWPLAN_ALL
SQLGetDiagRec

System Stored Procedures (Replication)

sp_replstatus

The DUMP statement created database or transaction log backups (dumps).

The DUMP DATABASE and DUMP TRANSACTION statements are synonymous with BACKUP DATABASE and BACKUP LOG statements. Support for the DUMP DATABASE and DUMP TRANSACTION statements may be removed in a future release.

Consider removing all references of DUMP DATABASE and replacing with references to BACKUP DATABASE. Consider removing all references of DUMP TRANSACTION and replacing with references to BACKUP LOG.

The CREATE DATABASE …FOR LOAD statement syntax created a destination database before its restoration from a database backup and prevented anyone from using the database between the CREATE DATABASE, ALTER DATABASE, and LOAD statements.

The CREATE DATABASE …FOR RESTORE syntax is supported for backward compatibility only. However, because SQL Server 7.0 now creates the destination database within a restore operation, it is recommended that the destination database not be created before executing the restore operation.

Do not create the database prior to restoring it.

The publish option of sp_dboption enabled or disabled publishing in a database.

publish can still be used to enable or disable publishing in a database. However, this option is supported for backward compatibility only. It is recommended that sp_replicationdboption be used for enabling or disabling publishing in a database.

Consider removing all references of sp_dboption publish and replacing with references to sp_replicationdboption.

DBCC NEWALLOC checked data and index pages against corresponding extent structures.

DBCC NEWALLOC is supported for backward compatibility only and is identical to DBCC CHECKALLOC.

Consider removing all references of DBCC NEWALLOC and replacing with references to DBCC CHECKALLOC.

DBCC TEXTALL selected tables in the database that had text or image columns and ran DBCC TEXTALLOC on them.

DBCC CHECKDB checks the consistency of text, ntext, and image columns in a database. DBCC TEXTALL is available for backward compatibility only.

Consider removing all references of DBCC TEXTALL and replacing with references to DBCC CHECKDB.

DBCC DBREPAIR dropped the specified, and usually damaged, database.

Use DROP DATABASE to drop or remove a SQL Server database. DBCC DBREPAIR is available for backward compatibility only.

Consider removing all references of DBCC DBREPAIR and replacing with references to DROP DATABASE.

DISK INIT created database or transaction log devices. When DISK INIT followed either a CREATE DATABASE or ALTER DATABASE statement, SQL Server used the specified device(s) for storing the specified database or transaction log.

The CREATE DATABASE statement syntax and ALTER DATABASE statement syntax both allow the creation of separate data and log files. Both CREATE DATABASE and ALTER DATABASE create operating system files and databases in a single step (generating a log file automatically, if none is specified with the LOG ON clause).

Consider removing all references to DISK INIT and replacing with references to either CREATE DATABASE or ALTER DATABASE.
DISK INIT has limited support in SQL Server 7.0; existing scripts will run as long as they do not have data and log sharing the same data file(s).

sp_logdevice put syslogs (contains the transaction log) on a separate database device. To add another log segment to a database with an existing log segment, it was necessary to execute DISK INIT followed by sp_logdevice.

Removed. The CREATE DATABASE statement creates a log file on a new operating system file.

Consider removing all references to sp_logdevice and replacing with references to CREATE DATABASE. Pre-SQL Server 7.0 scripts using the LOG ON clause of CREATE DATABASE will work as expected. Scripts without the LOG ON clause of CREATE DATABASE will have a log file generated automatically.

Server Initialization and Configuration

srv_config
srv_config_alloc
SRV_GETCONFIG
srv_init
srv_run
SRV_TDSVERSION

Error and Message Handling

srv_errhandle
SRV_IODEAD
srv_log
srv_sendstatus
srv_sfield

Event Types

SRV_ATTENTION
SRV_CONNECT
SRV_DISCONNECT
SRV_LANGUAGE
SRV_RPC
SRV_EXIT
SRV_START
SRV_SLEEP
SRV_RESTART
SRV_STOP

Remote Stored Procedure Event Processing

srv_paramdata
srv_paramlen
srv_parammaxlen
srv_paramname
srv_paramnnumber
srv_paramset
srv_paramstatus
srv_paramtype
srv_returnval
srv_rpcdb
srv_rpcnumber
srv_rpcoptions

Memory Management

srv_alloc
srv_bmove
srv_bzero
srv_free

Multiple header files were used in Open Data Services, including:
· Srvapi.h
· Srvconst.h
· Srvdbtyp.h
· Srvmisc.h
· Srvstruc.h
· Srvtok.h
· Srvtypes.h

The contents of these header files have been consolidated into srv.h. When recompiling a gateway applications, only srv.h must be included.

Consider removing all references to SQL Server 6.x header files and replacing with references to the srv.h header file.

srv_describe

srv_describe does not support data conversion to or from the new SQL Server 7.0 data types.

Consider removing or commenting out all references to srv_describe when using data types new to SQL Server 7.0.

srv_paramdata*
srv_paramlen*
srv_parammaxlen*
srv_paramtype*

These extended stored procedure functions have been superseded by srv_paraminfo.

Consider removing all references of srv_paramdata, srv_paramlen, srv_parammaxlen, or srv_paramtype and replacing with references to srv_paraminfo.

* These extended stored procedure functions will support some of the new SQL Server 7.0 data types and return data compatible with version 6.x format whenever possible. For example, if a parameter is nchar(25) and srv_paramdata accesses the data, srv_paramdata returns the data as char(25). For obtaining new SQL Server 7.0 data types, use srv_paraminfo and srv_paramsetoutput.

Queries could include a server user ID (SUID) without performance implications, as shown in the following table.

Queries using SUIDs continue to run and produce the same results as in earlier versions of SQL Server. However, there is a severe performance penalty because SUIDs are no longer native to the new security design.

Consider removing all references to SUIDs and replacing with references to SIDs (as shown in the following table) to avoid degradation in query performance.

SUSER_ID

SUSER_SID, which returns a SID

syslogins.suid

syslogins.sid

sysremotelogins.suid

sysremotelogins.sid

sysalternates.suid

sysusers.isaliased

The ON {table | view} ( column [,…n]) syntax for the GRANT statement assigned the specified permissions to the column(s) given for the specified table or view.

The ON {table | view} ( column [,…n]) syntax for the GRANT statement is supported for backward compatibility only.

Consider using the SQL-92 standard GRANT syntax for object permissions, and placing the column list before the ON clause.

The SETUSER statement allowed a database owner to impersonate another user.

SETUSER is included in Microsoft SQL Server version 7.0 for backward compatibility only, and is not recommended to be used. This statement may no longer be supported in a future release of SQL Server.

Consider removing or commenting out all references to SETUSER.

The FASTFIRSTROW optimizer hint caused the optimizer to use the nonclustered index if one matches the ORDER BY clause.

The OPTION (FAST n) query hint replaces FASTFIRSTROW. However, FASTFIRSTROW is maintained for backward compatibility only.

Consider removing all references to FASTFIRSTROW in SELECT statements and replacing with references to OPTION (FAST n).

The SET SHOWPLAN statement returned output as informational messages through SQLGetDiagRec in ODBC, or through the message handler in DB-Library applications.

The SET SHOWPLAN statement is no longer supported. It has been replaced by SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. The output of SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL is returned not as informational messages, but as a result set.

Consider removing all references of SET SHOWPLAN and replacing with references to either SET SHOWPLAN_TEXT (to display readable text) or SET SHOWPLAN_ALL (to display output that can be parsed more easily by an application building a report of showplan output). The application needs to process the output as part of the result set, not as messages returned through the ODBC SQLGetDiagRec function or the DB-Library message handler.

xp_grantlogin and xp_revokelogin granted or revoked SQL Server access to a Windows NT-based group or user.

Use sp_grantlogin and sp_revokelogin even though xp_grantlogin and xp_revokelogin are supported for backward compatibility only.

Consider removing all references of xp_grantlogin and xp_revokelogin and replacing with references to sp_grantlogin and sp_revokelogin, respectively.

sp_replstatus updated the internal table structure for replication.

Even though sp_replstatus is still available, support for this stored procedure may not be available in a later release.

Consider using the minimum number of references to sp_replstatus.

Job management was called task management, and several system stored procedures allowed system administrators to create and manage tasks.

The task management system stored procedures are no longer documented and are included for backward compatibility only.

Even though SQL Server 7.0 supports the task management system stored procedures (sp_addtask, sp_droptask, sp_helptask, sp_reassigntask, and sp_updatetask) for scheduling and managing SQL Server jobs, consider using either SQL Server Enterprise Manager or the job-related system stored procedures listed in the following table be used for managing jobs.

sp_addtask

sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_start_job

sp_helptask

sp_help_jobhistory
sp_help_jobschedule
sp_help_jobstep

sp_updatetask

sp_update_job
sp_update_jobstep
sp_update_jobschedule  

Aliases

Roles
Managing Permissions

Configuration

sp_configure (media retention option)
Setting Configuration Options

Data Types

decimal and numeric
Using Mathematical Functions
+ (Add)
- (Subtract)
* (Multiply)
/ (Divide)
ATN2
AVG
CAST and CONVERT
EXP
POWER
RADIANS
ROUND
SUM

DBCC

DBCC

DELETE and SELECT

FROM

Functions

@@DBTS

ODBC

SQL_COPT_SS_PERF_QUERY_INTERVAL
SQLMoreResults
SQL_NO_DATA

Rebuilding the Registry

setup /t
regrebld Utility

Security

SYSTEM_USER

Triggers and System Stored Procedures (System)

CREATE TRIGGER
sp_dboption (recursive triggers option) 
sp_tableoption
xp_readmail
xp_sendamil

Utilities

SQL Server Query Analyzer
isql utility

An alias allowed a user to temporarily assume the identity of another user within a database and perform actions as the aliased user. For example, the database owner could be aliased to a user so they could act as that user, if the user were on vacation.

Roles have replaced aliases. Because a user can belong to more than one role at a time, it is no longer necessary to assume the identity of another. Users belonging to the same roles have the same permissions automatically, assuming permissions are only applied at the role level, not the user level.

Expect different results as compared to earlier versions of SQL Server. Use roles instead of aliases. For more information about database roles, see "Managing Permissions" in Microsoft SQL Server Administrator's Companion. 

The LOAD HEADERONLY statement retrieved a result set detailing the header information from a database dump.

The result set returned for the LOAD HEADERONLY statement has changed.

Expect different results as compared to earlier versions of SQL Server. Expect a different result set from LOAD HEADERONLY, compared to pre-SQL Server 7.0 versions of LOAD HEADERONLY. For more information about LOAD HEADERONLY, see "LOAD" in Microsoft SQL Server Building Applications. 

Administration scripts may have used the nonadvanced media retention option to set the number of days to retain each backup medium after it has been used for a database or transaction log dump.

media retention is now an advanced option.

Expect different results as compared to earlier versions of SQL Server. Expect the media retention configuration option to appear only if you have enabled the advanced configuration options of sp_configure.

CREATE PROCEDURE statements failed if they contained a CREATE TABLE or SELECT INTO statement creating a temporary table with the same name as a temporary table that existed at the time the CREATE PROCEDURE statement was executed.

The CREATE PROCEDURE statement succeeds.

Recode any logic that depended on the earlier behavior.

The decimal and numeric data types could use anywhere from 2 through 17 bytes to store a value, depending on the precision of the stored value.

numeric and decimal now use 5, 9, 13, or 17 bytes of storage.

Expect different results as compared to earlier versions of SQL Server. Make sure that databases using numeric or decimal data types have sufficient storage for the change in storage bytes.

A negative second parameter equal to the number of digits in the ROUND expression returned a value of 0 for integer, float, and money data types. When the second parameter was negative and less than the number of digits in the ROUND expression, ROUND returned a value that rounded the right-most digit down to 0.

When the second parameter in the ROUND function is negative for all data types, SQL Server returns a value rounded up to the next highest value, respecting decimal places, where appropriate.

Expect different results as compared to earlier versions of SQL Server. Expect different results with the ROUND function, compared to earlier versions of SQL Server, when the second parameter is negative.

The SET DATEFIRST setting of the DATEPART function had no effect on the week datepart.

The week datepart may give values different from earlier versions of SQL Server. However, any difference will appear only if the SET DATEFIRST setting is not the default (the U.S. English default is 7, or Saturday).
If the year provided in the DATEPART function has 366 days, a week value of 54 can be returned if the first week of the year starts on a Saturday, and the year ends on the same day of the week with the first day of the week counted from Sunday.
When using the ISO 8601 standard, week values are always from 1 through 53, as the first week of the year is guaranteed to have a minimum of 4 days.

Expect different results as compared to earlier versions of SQL Server. Use the default value for SET DATEFIRST so that DATEPART returns the expected results for the week datepart. Otherwise, DATEPART values will be one less than expected.

Each DBCC statement had a certain output format.

The output formats of many DBCC statements have changed.

Expect different results as compared to earlier versions of SQL Server. Expect changes in output from DBCC statements as compared to pre-SQL Server version 7.0 DBCC statements.

The DBCC PERFMON and DBCC SQLPERF statements documented SQL Server performance statistics used for studying SQL Server performance.

No longer documented. These statements may change in a future release of SQL Server.

Use the SQL Server Performance Monitor to monitor the performance counters for SQL Server. For more information, see "Monitoring with Windows NT Performance Monitor" in Microsoft SQL Server Administrator's Companion. 

When comparing DBCS space characters, the Unicode A140 space character (U-A140) was not equal to the Unicode 0020 (U-0020) space character.

Comparisons involving the Unicode A140 space character (U-A140) are now equivalent to the Unicode 0020 (U-0020) space character.

Expect different results as compared to earlier versions of SQL Server when comparing DBCS space characters.

Duplicate table names in the FROM clause of a SQL Server version 6.0 DELETE or SELECT statement caused SQL Server to treat both table references as the same table. SQL Server discarded the reference to the second authors table in this SELECT example:
USE pubs
GO
SELECT *
FROM authors, authors
GO
 

Duplicate table names in the FROM clause of a DELETE or SELECT statement generated errors in SQL Server. Rewrite statements using aliases. Here is a SELECT example:
SELECT *
FROM pubs..authors AS
a1, pubs.dbo.authors
AS a2

USE pubs
SELECT *
FROM authors AS au1, authors AS au2

Expect different results as compared to SQL Server version 6.0. Rewrite DELETE and SELECT statements to use aliases in the FROM clause when referring to more than one instance of the same table.

The DISK RESIZE statement altered the size of a database device.

The DISK RESIZE statement is supported, but may not be supported in future releases. In addition, the DISK RESIZE statement does not alter the size of the database. Instead, use ALTER DATABASE.

Expect different results as compared to earlier versions of SQL Server. Use the MODIFY file_decl clause of the ALTER DATABASE MODIFY statement to alter the size of a database.

The @@DBTS global variable was incremented any time any page in the database was modified in any way.

The value returned by @@DBTS changes only if a row containing a timestamp column is modified.

Expect different results as compared to earlier versions of SQL Server when using @@DBTS.

Global variables were those system-supplied, predeclared variables that were distinguished from local variables by having two at symbols (@@) preceding their names.

Transact-SQL global variables are a form of function and are now referred to as functions.

Expect different results as compared to earlier versions of SQL Server. For more information, see "Functions" in Microsoft SQL Server Building Applications.

In the version 2.65 ODBC driver, the long-running query interval, specified by calling SQLSetConnectOption with the driver-specific connection option SQL_COPT_SS_PERF_QUERY_INTERVAL, was specified in seconds.

The SQL_COPT_SS_PERF
_QUERY_INTERVAL value is specified in milliseconds.

Expect different results as compared to earlier versions of SQL Server. Multiply the value of SQL_COPT_SS_PERF_QUERY_INTERVAL by 1,000 to convert the number of seconds to milliseconds. For more information about SQL_COPT_SS_PERF_QUERY_INTERVAL, see "SQLSetConnectAttr" in Microsoft SQL Server Building Applications. 

Earlier versions of the ODBC SQL Server driver returned SQL_SUCCESS when executing a searched UPDATE or DELETE statement that affects no rows (using SQLExecute, SQLExecDirect, or SQLParamData). SQLRowCount returned zero.

When an ODBC version 3.x application uses the ODBC SQL Server 3.5 driver included with this release, it returns SQL_NO_DATA when executing a searched UPDATE or DELETE statement that affects no rows (using SQLExecute, SQLExecDirect, or SQLParamData). SQLRowCount still returns zero.

Expect different results as compared to earlier versions of SQL Server. Handle SQL_NO_DATA appropriately.

Executing SQL Server Setup rebuilt the master database.

No longer supported. SQL Server includes the Rebuild Master (rebuildm) utility.

Use the Rebuild Master (rebuildm) Utility located in the \Mssql7\Binn folder to rebuild the master database.

Executing this SQL Server Setup rebuilt the registry:
setup/t
RegistryRebuild = On.

No longer supported. SQL Server includes the regrebld utility for backing up and restoring the registry.

Use the regrebld utility located in the \Mssql7\Binn folder to rebuild the registry.

Restricted publications could be created through SQL Server Enterprise Manager and used in replicating data.

No longer supported.

Expect different results as compared to earlier versions of SQL Server. Remove all references to restricted publications. A replacement for restricted publications will be available in a later release.

The SYSTEM_USER niladic function returned nulls for any Microsoft Windows NT login.

The appropriate Windows NT domain and login names are returned if Windows NT Authentication is used with the SYSTEM_USER function.

Expect different results as compared to earlier versions of SQL Server. Expect a different result, as compared to earlier versions, when using SYSTEM_USER with Windows NT Authentication.

The *= and =* operators (left outer join and right outer join, respectively) were used in SELECT statements to produce left and right outer joins, respectively.

The SQL-92-standard syntax of LEFT OUTER JOIN and RIGHT OUTER JOIN is preferred. However, pre-SQL Server 7.0 join operators are supported.

Expect different results as compared to earlier versions of SQL Server. Remove all references of the *= (left outer join) and =* (right outer join) operators in all SELECT statement FROM clauses and replace with references to the SQL-92-standard syntax RIGHT OUTER JOIN and LEFT OUTER JOIN. Future versions of SQL Server will support only the SQL-92-standard syntax.

Returned values were not always correct for text or image columns in either the inserted or deleted tables when either table was used in a CREATE TRIGGER statement.

NULL values are returned for text or image column references in the inserted or deleted tables in CREATE TRIGGER.

Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to either the text or image columns of the inserted or deleted tables when used in CREATE TRIGGER statements.

Server-to-server communication existed between version 4.x and version 6.x servers when initialized by either side.

Version 4.x or 6.x servers can communicate with SQL Server 7.0 servers. However, server-to-server communication is not supported from SQL Server 7.0 servers to 4.x servers.

Expect different results as compared to earlier versions of SQL Server. Upgrade the 4.x server to either SQL Server 6.x or SQL Server 7.0.

The @message parameter of xp_readmail was varchar(255). The @message and @query parameters of xp_sendmail were varchar(255).

The @message and @query parameters are now varchar(8000).

Expect differences in behavior as compared to earlier versions of SQL Server when using the @message parameter of xp_readmailand the @message and @query parameters of xp_sendmail.

In SQL Server version 6.0, the following UPDATE statement, using two different table aliases for the same base table, was allowed:
CREATE TABLE t1 (c1 int)
GO
INSERT t1 VALUES (1)
INSERT t1 VALUES (2)
GO
UPDATE t1
SET c1 = 50
FROM t1 a1, t1 a2
WHERE a1.c1 = 1 AND
a2.c1 = 2
GO

No longer supported. Use the table alias following the UPDATE keyword of the UPDATE statement rather than the table name. The UPDATE statement would be rewritten to:
UPDATE a1
SET c1 = 50
FROM t1 a1, t1 a2
WHERE a1.c1 = 1 AND
a2.c1 = 2

Expect differences in behavior as compared to SQL Server version 6.0 when referring to a table by its table name rather than a table alias following the UPDATE keyword of the UPDATE statement.

isql/w used DB-Library. The SQL-92 settings, like ANSI_WARNINGS, were set off, by default.

SQL Server Query Analyzer uses the SQL Server ODBC driver, which, by default, sets these SQL-92 options on: SET ANSI_WARNINGS, SET ANSI_PADDING, and SET ANSI_NULLS. Any errors returned are formatted as ODBC errors rather than DB-Library errors.

Expect different results as compared to earlier versions of SQL Server. Expect different results with SQL Server Query Analyzer, compared to the isql utility or the SQL Server 6.5 version of isql/w. These SQL-92 options can also be turned off on the Query Current Connections Options menu.