How to bridge the gap
While developing SQL Server 7.0, Microsoft was so concerned about backward compatibility that one of its design goals included the capability for all existing application source code to run unmodified. However, as with most product upgrades, in order for the company to implement several new features, it had to sacrifice some backward compatibility. In this article, we explore some of SQL Server's internal implementation changes that resulted in potential compatibility problems and examine several situations that developers might face when upgrading applications.
Why All the Changes?
With any new software release, you expect change. SQL Server 7.0 provides change and more. Internally, SQL Server 7.0 is almost a completely new product. Why the drastic change? Put simply, the architecture and code base for SQL Server had reached their limits with the 6.5 release. The only way to achieve the long list of features and design goals for this release and still position SQL Server with enough growth room was to reengineer the internals completely. Doing so created an opportunity for Microsoft to enhance the efficiency of the query optimizer by adding new algorithms and to increase ANSI compatibility by tightening many of the Transact SQL (T-SQL) constructs. These two changes most directly contribute to the compatibility conflicts in moving to SQL Server 7.0.
Let's examine some changes in SQL Server 7.0 and how they affect compatibility. We'll categorize the issues into three groups: system-level, syntactical, and behavioral.
System-Level Compatibility
Knowing that backward compatibility is a large concern for customers, Microsoft incorporated the concept of compatibility levels into SQL Server 7.0. SQL Server 7.0 has three compatibility levels: 60 for SQL Server 6.0 databases, 65 for SQL Server 6.5 databases, and 70 for SQL Server 7.0 databases. These levels let legacy databases in the 7.0 environment operate as if they were running under an earlier release of the product.
The system stored procedure sp_dbcmptlevel lets developers report on and change the compatibility level of their databases. For example, to set the Northwind database to level 65 compatibility, execute the following command:
sp_dbcmptlevel [Northwind], 65
In SQL Server 7.0, the Master database must always be at the 70 compatibility level. If you add any user-defined objects to Master, you must test and ensure that they work correctly at the 70 level. However, you can set other user databases within the server to the 60 or 65 compatibility levels. This functionality lets developers migrate their existing databases to SQL Server 7.0 in phases. For example, a development environment might support a new project that uses a version 70 database while simultaneously supporting two older projects that use one database at the 60 compatibility level and another at the 65 compatibility level.
For databases at a 60 or 65 compatibility level, many of SQL Server 7.0's new features are not available. Also, although SQL Server 7.0 currently supports these legacy databases, future releases might not. Therefore, we strongly recommend that you upgrade all applications and databases to support 7.0 as soon as possible.
System Tables and Information Schema Views. Microsoft has always frowned on developers directly accessing SQL Server's internal system tables. With each release of SQL Server, the company provides a disclaimer that tells users to access system tables only via one of the provided APIs: system stored procedures, SQL Distributed Management Objects (SQL-DMO), or T-SQL. The reason for this warning is that the format of the system tables depends on the internal architecture of SQL Server and might change from release to release. However, until SQL Server 7.0, Microsoft made few changes to these tables, and consequently, many developers incorporated system tables into their applications.
Developers who did not heed Microsoft's warning are in for a rude awakening. Many of the system tables' structures have drastically changed. In addition, SQL Server 7.0 storage format is natively Unicode; consequently, system meta data now uses the new Unicode data format. For example, SQL Server 7.0 identifiers are all nvarchar(128). During beta testing, system-table changes gave sites the most problems in upgrading their applications. To give some compatibility, SQL Server 7.0 provides system-level views that mimic the 6.x system tables. Although these views exist in all compatibility modes, they are for backward compatibility only and do not fully support many of the new features.
In SQL Server 7.0, meta data management is still available through system stored procedures and SQL-DMO. In addition to these two access methods, SQL Server now supports the SQL-92 definition for the Information Schema. The Information Schema is a series of read-only views that provide an internal, system table-independent view of the SQL Server meta data. Developers who need to access system information but are concerned about database portability can use these views instead of the system tables.
Syntactical Compatibility
Many syntax changes that occurred in the 7.0 release (e.g., the addition of the TOP keyword, increased data-type sizes, and increased object-name size limits) involve the addition of new features. Fortunately, those changes have little impact on your migration strategy. However, a few other changes are worth mentioning.
UPDATE and Aliases. One significant difference between SQL Server 6.5 and 7.0 is the use of aliases in an UPDATE statement. When the FROM clause contains an alias and the SET clause references that alias, SQL Server 7.0 does not let the UPDATE clause reference the table name. The UPDATE clause must reference the alias instead. For example, Listing 1 shows a SQL statement that works in SQL Server 6.5, but when you execute it in SQL Server 7.0, the compiler reports an error. Listing 2 shows the syntax that works in both the 6.5 and 7.0 environments.
Foreign Keys. The data types of foreign-key columns must be the same between tables. Before SQL Server 7.0, this requirement applied to numeric data, but SQL Server allowed char and varchar key combinations. Listing 3 is an example of a foreign-key declaration that is valid in SQL Server 6.5, but fails in SQL Server 7.0 (unless it's running in 65 compatibility mode). SQL Server 6.5 provided the implicit type conversion between the two types. In SQL Server 7.0, the types must exactly match. Updated versions of the creation statements appear in Listing 4.
Deferred Name Resolution. Deferred name resolution allows procedure compilation without all table references being present. Deferred name resolution works in much the same way as the object-oriented concept of late binding. At compile time, the compiler attempts to resolve all table names that the procedure references. But if a table does not yet exist, the compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures or triggers, this subtle new feature is long overdue. Although this feature is useful, it has a side effect that many developers might initially miss--the compiler no longer reliably catches table-name typos. Yes, you now must test your code. This statement might sound funny at first, but if you are not aware of deferred name resolution, you might find yourself wondering why the compiler missed this error.
Outer Joins. In earlier versions of SQL Server, you use the *= and =* operators to specify the left and right outer join conditions in the WHERE clause. In some cases, this syntax results in an ambiguous query that might not return the expected results. For example, consider the two SELECT statements in Listing 5 and their output in Figure 1 on page 32. Logically, both statements are identical, but they result in different data sets. The ambiguity exists under the old syntax because the optimizer does not always distinguish the JOIN condition from the selection criteria. SQL Server 6.5 supports SQL-92-compliant outer joins using the LEFT OUTER JOIN and RIGHT OUTER JOIN extensions in the FROM clause, thus removing this ambiguity. For backward compatibility, SQL Server 7.0 continues to support the old-style syntax. However, Microsoft has stated that future releases of SQL Server will support only the SQL-92 syntax. Therefore, we strongly recommend that you convert any remaining queries that use the old-style syntax to a SQL-92-compliant form.