Microsoft® SQL Server™ 2000 is compatible with SQL Server version 6.5 in many respects. Most product functionality of SQL Server version 6.5 remains in SQL Server 2000. Most applications for SQL Server 6.5 work unchanged after the database server is upgraded to SQL Server 2000 by the SQL Server Upgrade Wizard.
The SQL Server 2000 upgrade process:
- Adds functionality, either new to SQL Server 2000 or changed from earlier versions, which makes tasks easier to accomplish.
- Minimizes the time and effort needed to upgrade.
In some cases, compatibility issues can arise:
- Configuration Options
Some server configuration options have changed.
- SQL-DMO, Tasks, and Replication
Task, replication, and device objects have changed. SQL Server 2000 uses jobs instead of tasks, and provides new system tables and system stored procedures.
- Replication and Triggers
Replication types that allow data modifications at the Subscriber use triggers to track changes to published tables. If there are triggers on your application that modify published tables, the sp_configure server option nested triggers should be enabled. This option affects tables used in merge replication or tables used in snapshot replication or transactional replication with the immediate updating or queued updating option. Before adding these types of replication to an existing database that uses triggers, be sure your application works correctly with the nested triggers option enabled. The nested triggers option is enabled by default; however, if this option was disabled previously, you will need to enable it again.
- Segments and Devices
SQL Server 7.0 and SQL Server 2000 use 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.
- System Tables
If your applications depend upon accessing system tables directly, the applications may need to be revised. It is recommended that you use system stored procedures or information schema views.
Here are the SQL Server 6.x system tables that are not included with SQL Server 2000.
master.dbo.spt_datatype_info sysprocedures sysbackupdetail sysrestoredetail sysbackuphistory sysrestorehistory syshistory syssegments syskeys systasks syslocks sysusages
- Backup and Restore
SQL Server 2000 uses BACKUP and RESTORE statements in place of DUMP and LOAD. DUMP and LOAD are supported for backward compatibility, but with some limitations.
- System Stored Procedures
Some system stored procedures are no longer supported.
For more information, see the discussion of specific backward compatibility issues.
Setting a Backward Compatibility Level
When running at its default settings, Microsoft SQL Server 2000 implements SQL-92 behaviors for some Transact-SQL statements whose behaviors differed from the standard in earlier versions of SQL Server. SQL Server 2000 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 applications in an orderly fashion. Most applications, however, are not affected by the changes in behavior and work at the SQL Server 2000 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), 70 (version 7.0 compatibility), and the default 80 (SQL Server 2000 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 2000. Applications still benefit from features such as the improved query processor. For more information, see the discussion of specific behaviors controlled by the different settings in sp_dbcmptlevel.
For installations of all instances of SQL Server 2000, the default level for all databases is 80. For upgrades from SQL Server 7.0 to SQL Server 2000, the default level for all databases is 80. For upgrades from SQL Server 6.5 and SQL Server 6.0 to SQL Server 2000, the existing default compatibility level is retained.
Important The compatibility level for the master database is 80 and cannot be changed. If you have added any user-defined objects to master, you must ensure they work correctly at the 80 compatibility level.
The model database is set automatically to the SQL Server 2000 compatibility level 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 2000 behavior in new databases created after an upgrade, use sp_dbcmptlevel to change the compatibility level setting in model.
Certain 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 valid. 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.
Likewise, setting the compatibility level of a database to 65 makes the database version-6.5 compatible, but does not necessarily provide version 6.5 behaviors. For example, when SET ANSI_PADDING is ON and you attempt to insert the strings 'abc' and 'abc ' into a primary key column, SQL Server 2000 considers the strings to be duplicates and does not violate the primary key constraint. In SQL Server 6.5, the two strings are considered to be unique and both insertions succeed. Setting the compatibility level to 65 does not force SQL Server 2000 to treat the strings as unique values.
Note While running at compatibility level 60 or 65 preserves legacy behaviors on SQL Server 2000, 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 80 as soon as is practicable.