Backward Compatibility Details (Level 3)

Backward Compatibility Details (Level 3) consists of 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. It is recommended that, as time allows, the backward compatible item be replaced with the recommended item. Microsoft® SQL Server™ version 7.0 provides features that accomplish these tasks more efficiently and have ongoing support.

This topic covers backward compatibility details for these items.

Backup and Restore (Level 3)

Database Options (Level 3)

DBCC (Level 3)

Devices (Level 3)

In Microsoft® SQL Server™ 7.0, the following changes have been made to database architecture:

• Operating system files replace database devices.
• Data files and transaction logs cannot co-exist on the same operating system file.
• A single operating system file cannot be shared by multiple databases.

For more information about database architecture, see Overview of SQL Server Architecture.

Examples

A.Use both DISK INIT and CREATE DATABASE syntax

This example uses DISK INIT and CREATE DATABASE and works in SQL Server versions 6.5 and 7.0:

DISK INIT name = 'testdb_data',

    physname = 'c:\testdb_data.dat',

    vdevno = 9,

    size = 10240

DISK INIT name = 'testdb_log',

    physname = 'c:\testdb_log.dat',

    vdevno = 8,

    size = 10240

CREATE DATABASE testdb

    ON testdb_data = 10

    LOG ON  testdb_log = 10

GO

B.Use sp_logdevice and CREATE DATABASE in 7.0 fails

In pre-SQL Server 7.0, this script created a 20 MB database consisting of the two files named testdb_data and testdb_log. This script also moved the transaction log to the testdb_log device by using sp_logdevice.

Note Scripts like this one were usually generated by the SQL Server 6.5 sp_help_revdatabase system stored procedure, which used sp_logdevice to ensure the proper device layout for database restores. Because SQL Server 7.0 creates the database when it is restored, scripts such as these are no longer necessary.

-- Pre-SQL Server 7.0 example.

DISK INIT name = 'testdb_data',

    physname = 'c:\testdb_data.dat',

    vdevno = 9,

    size = 10240

DISK INIT name = 'testdb_log',

    physname = 'c:\testdb_log.dat',

    vdevno = 8,

    size = 10240

CREATE DATABASE testdb on testdb_data = 10, testdb_log = 10

-- Use sp_logdevice to move the log to the testdb_log device.

sp_logdevice testdb, testdb_log

In SQL Server 7.0, the above script does not work the same as in pre-SQL Server 7.0 because sp_logdevice no longer exists.

In SQL Server 7.0, this script creates a 20 MB database consisting of the two files named testdb_data and testdb_log. In addition, SQL Server generates a log file automatically, which is 25 percent of the database size. In the following script (using the devices created earlier), a 10 MB log file is generated automatically:

CREATE DATABASE testdb on testdb_data = 10, testdb_log = 10

C.Use CREATE DATABASE syntax only

Using the SQL Server 7.0 CREATE DATABASE syntax, the database from the earlier example could be created as follows:

CREATE DATABASE testdb ON (name = 'testdb_data',

    filename = 'd:\testdb_data.dat', size = 10)

    LOG ON (name = 'testdb_log', filename = 'd:\testdb_log.dat',

    size = 10)

Open Data Services (Level 3)

Important When upgrading an existing Open Data Services application to SQL Server 7.0, it is recommended that you recompile all existing Open Data Services applications with the SQL Server 7.0 Open Data Services header file (Srv.h) and library file (Opends60.lib). For more information about Open Data Services, see Backward Compatibility Details (Level 1).

The Open Data Services gateway functions, macros, and events listed in the table are supported for backward compatibility only.

Query Performance (Level 3)

Examples

A.Use SIDs and SUIDs to display login names of users in sysusers

This example shows a SQL Server 6.x query that displayed the login names of all users in sysusers:

SELECT L.name

FROM master.dbo.syslogins L, sysusers U

WHERE L.suid = U.suid

-- Or

SELECT suser_name(suid) AS name

FROM sysusers

Here is the earlier query rewritten to use SIDs rather than SUIDs:

SELECT L.loginname

FROM master.dbo.syslogins L, sysusers U

WHERE L.sid = U.sid

-- Or

SELECT suser_sname(sid) AS name

FROM sysusers

B.Use SIDs and SUIDs to display aliased usernames

This example shows a SQL Server 6.x query that displayed the login names of all logins aliased to some other login:

SELECT suser_name(suid) AS name

FROM sysalternates

Here is the earlier query rewritten to use SIDs rather than SUIDs:

SELECT suser_sname(sid) AS name

FROM sysusers

WHERE isaliased = 1

Security (Level 3)

SELECT (Level 3)

SET SHOWPLAN (Level 3)

System Stored Procedures (Extended) (Level 3)

System Stored Procedures (Replication) (Level 3)

System Stored Procedures (System) (Level 3)

The task-related stored procedures listed in the Pre-SQL Server 7.0 column below have been replaced by the corresponding job-related stored procedures shown in the SQL Server 7.0 column.