Database Compatibility in SQL Server 7.0

SQL Server 7.0 has built-in backward compatibility. When you upgrade the databases to SQL Server 7.0, they are left in a backward compatible state. If you upgrade from SQL Server 6.0, the databases are in 6.0 mode. If you upgrade from SQL Server 6.5, the databases are in 6.5 mode. Databases in native SQL Server 7.0 mode are said to be in 7.0 mode.

Backward compatibility modes allow your application to function as it did in SQL Server 6.x. For example, the TOP keyword in SQL Server 7.0 is not valid in a SQL Server 6.x backward compatibility mode, because this word may have been used as a column name, a column alias, or in another context that is not valid in SQL Server 7.0.

In addition to syntax changes, some functional behaviors are affected also. In SQL Server 6.x, a SELECT statement with a GROUP BY clause always implies an ORDER BY on the same columns. In SQL Server 7.0, this is not the case; however, in SQL Server 6.x backward compatibility modes, an ORDER BY is implicitly added to GROUP BY queries so that the data is returned in the same sequence that it would have been in SQL Server 6.x. For more information about backward compatibility, see SQL Server Books Online.

You can run the sp_dbcmptlevel system stored procedure to switch between backward compatibility modes. For example, if you upgrade a database named upgrade_test, you run exec sp_dbcmptlevel ‘upgrade_test’, 70 to change the database compatibility level to SQL Server 7.0 mode. No other database on the server is affected by this change. To switch back to SQL Server 6.5 mode instantly, simply run exec sp_dbcmptlevel ‘upgrade_test’, 65. This switching mechanism allows you to switch compatibility modes easily and test your application. If no problems are encountered, you can leave the database in 7.0 mode. If you do encounter issues that require coding changes, you can switch back to the compatibility mode of the earlier version until you have time to complete the code changes. You still get most of the performance enhancements and the other benefits of SQL Server 7.0 while running your applications in backward compatibility mode.


Note The master database must always be in 7.0 compatibility mode; therefore, applications that use the master database must change immediately when upgrading.