You have devoted time and financial commitments to your current installations of Microsoft® SQL Server™ and SQL Server applications. Microsoft understands the implications of a major upgrade like SQL Server version 7.0 and what this can mean to you and your customers.
Microsoft has made great efforts to ensure that your databases upgrade successfully and your applications run unchanged in SQL Server 7.0. You will have all the product functionality that you had prior to SQL Server 7.0. In other words, you do not need to change your applications in order to achieve the same level of functionality you had in versions prior to SQL Server 7.0. Your applications take advantage of most new SQL Server 7.0 capabilities automatically.
SQL Server 7.0 is highly backward compatible. Most 6.x applications work unchanged after being upgraded to SQL Server 7.0 by the SQL Server Upgrade Wizard. If your application does not have any of the following characteristics, you are unlikely to experience compatibility problems.
If your application sets 6.x configuration options directly using sp_configure or relies directly on configuration settings, see the discussion of configuration in SQL Server Backward Compatibility Details.
Task, replication, and device objects have changed. SQL Server 7.0 uses jobs instead of tasks, and provides new system tables and system stored procedures.
SQL Server 7.0 uses files and filegroups instead of segments and devices for storing indexes or tables. Unless your application depends upon the physical layout of segments within devices, this does not create compatibility problems for your application. For more information, see the discussions of segments and devices in SQL Server Backward Compatibility Details, and CREATE DATABASE, CREATE TABLE, and CREATE INDEX.
SQL Server 6.x provided system stored procedures as the correct way to access information stored in system tables. These system stored procedures are also provided as part of SQL Server 7.0 and are compatible with earlier versions of SQL Server. If your application bypasses the recommended system stored procedures by accessing system tables directly (for example, a query performing a SELECT involving a system table), see the discussion of system tables in SQL Server Backward Compatibility Details.
Here are the SQL Server 6.x system tables that are not included with SQL Server 7.0.
| master.dbo.spt_datatype_info | sysprocedures |
| sysbackupdetail | sysrestoredetail |
| sysbackuphistory | sysrestorehistory |
| syshistory | syssegments |
| syskeys | systasks |
| syslocks | sysusages |
SQL Server 7.0 uses BACKUP and RESTORE statements in place of DUMP and LOAD. DUMP and LOAD are supported for backward compatibility, but with some limitations. If your application uses DUMP or LOAD with the VOLUME option, or uses diskettes as the backup medium, see the discussion of BACKUP and RESTORE in SQL Server Backward Compatibility Details.
Some system stored procedures relating to the subjects discussed above are no longer available. If you have any of the above backward compatibility issues, see the discussion of stored procedures in SQL Server Backward Compatibility Details.
Any administrative tools using Transact-SQL syntax will continue to use Transact-SQL syntax properly. However, accessing system catalogs directly may not work. Most system catalogs from earlier versions of SQL Server are still available as views, but some of the system catalogs previously in the master database are no longer available. You do not need to change your scripts immediately; however, your applications and scripts will not yield all the benefits of the new syntax until you do.
Merge replication is implemented using recursive triggers and requires that the new sp_dboption recursive triggers option be enabled. Before adding merge replication to an existing database that uses triggers, make sure the triggers operate correctly with the recursive triggers option enabled. For more information, see the discussion of triggers in Programming Triggers.
For more information, see the discussion of specific backward compatibility issues in SQL Server Backward Compatibility Details.
When running at its default settings, Microsoft® SQL Server™ version 7.0 implements SQL-92 behaviors for some Transact-SQL statements whose behaviors differed from the standard in earlier versions of SQL Server. SQL Server 7.0 also enforces reserved keywords that were not keywords in earlier versions of SQL Server. If upgrading existing systems with existing applications, you can use the database compatibility level settings to retain the earlier behaviors if your existing applications depend on those behaviors. This gives you time to upgrade their applications in a planned, orderly fashion. Most applications, however, are not affected by the changes in behavior and work at the version 7.0 compatibility level.
The compatibility level is specified for each database using the sp_dbcmptlevel system stored procedure. The database compatibility level can be set to 60 (version 6.0 compatibility), 65 (version 6.5 compatibility) and the default of 70 (version 7.0 compatibility). The effects of the compatibility level settings are generally limited to the behaviors of a small number of Transact-SQL statements that also existed in earlier versions of SQL Server. Even when the database compatibility level is set to 60 or 65, applications gain almost all of the benefits of the new performance enhancements of SQL Server 7.0. Applications still benefit from features such as dynamic row-level locking and the improved query processor. For more information, see the discussion of specific behaviors controlled by the different settings in sp_dbcmptlevel.
An upgrade from an earlier version of SQL Server to version 7.0 sets the compatibility level of existing user-defined databases to the version of SQL Server from which you are upgrading. For example, if your server was a version 6.5 server before you upgraded to SQL Server 7.0, the compatibility level for all your existing user-defined databases is set to 65. This helps existing applications run unchanged after an upgrade.
Important Do not change the compatibility level setting for the master database. SQL Server 7.0 requires that the compatibility level of the master database always operates at the 7.0 level. If you have added any user-defined objects to master, you must ensure they work correctly at the 7.0 compatibility level.
The model database is set automatically to the SQL Server 7.0 compatibility level setting during an upgrade. All new user-defined databases are created with the same compatibility level setting as model. If you do not want to use any SQL Server 7.0 behavior in new databases created after an upgrade, use sp_dbcmptlevel to change the compatibility level setting in model. For more information, see the discussion of changing compatibility level settings in sp_dbcmptlevel.
Certain SQL Server 6.5 and 7.0 behaviors are not enabled at lower compatibility levels. For example, the keywords LEFT, OUTER, and JOIN are not keywords at compatibility level 60. This means the database compatibility level must be set to 65 or higher before the LEFT OUTER JOIN clause becomes legal. Before any applications can take advantage of features only available at a higher compatibility level, all applications using the database must be upgraded to work correctly at the higher compatibility level.
Note While running at compatibility level 60 or 65 preserves legacy behaviors on SQL Server 7.0, support for these behaviors may be dropped in future versions of SQL Server. It is recommended that you plan to upgrade your applications to work correctly with the compatibility level set to 70 as soon as is practicable.
| ALTER TABLE | SELECT |
| ALTER TRIGGER | SET ANSI_NULL_DFLT_ON |
| CREATE TRIGGER | sp_dboption |
| Reserved Keywords | UPDATE |