Migrating from SQL Server 6.5 to 7.0

Behavioral Compatibility
The behavioral category contains some of the hardest-to-identify compatibility changes. Generally, these subtle changes cause problems only at execution time. Further complicating matters, some of the conflicts produce errors or warning messages, but others might go unnoticed and lead to data corruption. The following items highlight some of the more frequent compatibility challenges developers encounter.

The Query Optimizer. A major change that affects backward compatibility is the addition of several new JOIN algorithms to the query optimizer. Before SQL Server 7.0, only one algorithm, nested iteration, was available. Each time a query executed, the result sets always returned in the same order. In SQL Server 7.0, the optimizer has several options: a hash join (in-memory, grace, or recursive), a merge join, or the nested iteration. Each of these alternatives can produce the correct result; however, each has the potential to return that result in a different order. For more information on how the query optimizer can improve query processing, see Dusan Petkovic and Christian Unterreitmeier, "New Features for Query Processing," July 1999.

If you rely on the implicit ordering of your data, you need to be especially conscious of this change. The change is most visible when you use the GROUP BY clause. Including a GROUP BY clause in your SELECT statement does not imply any ordering of the result set. Before SQL Server 7.0, developers often took for granted that the data would return in the same order as the columns in the GROUP BY clause. Therefore, they omitted the ORDER BY clause. SQL Server 7.0, like the ANSI standard, does not guarantee this ordering. If you need ordered data, you must supply the ORDER BY clause.

Also, because of the new join algorithms and internal changes (e.g., row-level locking, increased page size, and I/O and optimizer improvements), consider removing optimizer hints from your queries unless SQL Server 7.0 still requires the hints. Because of query-engine inefficiencies, earlier releases of SQL Server sometimes require that you use hints to get the best execution plan for large queries. The many enhancements to SQL Server 7.0 change this requirement. For maximum query efficiency, leave query-plan determination to the optimizer and use hints only as a last resort.

Null Comparisons. Dealing with three-state logic, which nulls introduce, has always been difficult for developers. Not only do queries have to consider the equality of an attribute, but when the attribute allows nulls, queries must also deal with the issue of the attribute's existence. When an attribute is null, its value is unknown. Therefore, logical comparison to another value is not possible. To get around this problem, earlier versions of SQL Server implement a T-SQL extension that allows for the comparison of nulls. For example, value=null was always true when value was null. This capability changes with SQL Server 7.0. In adhering to ANSI standards, SQL Server 7.0 uses the SET ANSI_NULLS option to control null comparisons. When set to ON, which is the default value, the statement value = null equates to unknown, not true or false. To test for the presence of nulls, you must now use the IS NULL or IS NOT NULL clause. Consider the SELECT statements in Listing 6: Under SQL Server 6.x, the first SELECT statement returns row "BB"; under SQL Server 7.0, it returns no rows. The second SELECT statement returns row "BB" under both SQL Server 6.5 and SQL Server 7.0.

Aggregation and Nulls. Another area involving nulls (and requiring good defensive programming) is aggregation. When SQL Server 6.5 encounters a null value in an aggregate function (such as AVG, MIN, and MAX), it skips the row and continues without generating any warning or error messages. Under SQL Server 7.0, the same statement completes but generates the message Warning: Null value eliminated from aggregate. For example, if you use the table definition and the first UPDATE statement Listing 7 shows, SQL Server 7.0 generates the warning. The second UPDATE statement correctly accounts for the nullibility of col2 and is preferred in all SQL Server versions.

String Truncation. SQL Server 7.0 no longer allows implicit truncation of strings. For example, consider the code in Listing 8, page 34. When you execute the code, SQL Server 6.5 does not generate any errors. However, the result set from the SELECT statement returns two rows; each contains the value 0123456789. If you execute that same SQL script under SQL Server 7.0, the second INSERT statement generates an error message, String or binary data would be truncated, and the statement ignores the INSERT. The result set returns only one row. The good news is that implicit data loss is eliminated. However, when moving data, developers and designers now must be more conscious of field sizes. Note that even when running under 65 compatibility mode, SQL Server 7.0 does not support 6.x behavior.

Arithmetic Overflow. In SQL Server 6.x, UPDATE and INSERT statements that encounter an arithmetic overflow condition set the value to null, or skip the action if the value belongs to a non-null column. In SQL Server 7.0, however, the action terminates and no modifications occur. For example, executing the SQL code in Listing 9, page 34, in SQL Server 6.x generates the warning Arithmetic overflow occurred, but the row still inserts with the col12 value set to NULL. Under SQL Server 7.0, these same SQL statements still produce an error message, Arithmetic overflow error for data type tinyint, Value = 1600000000. However, no row is inserted. As with string truncation, designers and developers must be conscious of this change and either adjust their data types or modify their calculations accordingly.

Empty Strings. Another new feature in SQL Server 7.0 is its ability to handle empty strings. Pre-7.0 releases interpret an empty string as either a null or a single blank character. In SQL Server 7.0, empty strings are treated as empty strings. This change affects all string-related functions. Some examples appear below; for a complete list of affected functions, consult SQL Server 7.0's Books Online (BOL).

Timestamps. Originally, the purpose of the timestamp data type was to support SQL Server recovery algorithms. Every time someone modified a page, SQL Server stamped it with the current @@DBTS value and incremented @@DBTS by one. This approach was sufficient to determine the relative sequence of page modifications for recovery, but the timestamp values have no relation to time. In SQL Server 7.0, the only remaining use of the timestamp data type is for concurrency, because the @@DBTS value is incremented only for use in timestamp columns. If a table contains a timestamp column, every time an INSERT, UPDATE, or DELETE statement modifies a row, the timestamp value in the row is set to the current @@DBTS value, and @@DBTS is incremented by one.

Also, you need to be aware of the effect of timestamps on replication. Merge replication does not support timestamp columns. Therefore, organizations that want to exploit SQL Server's merge replication features need to remove the timestamp column from any published table. In a merge replication scenario, changes to this column have no common meaning between servers, because local servers generate their own timestamp values, and the uniqueness of those values is guaranteed only within their specific databases.

Putting It to the Test
Each deployment and database is different. We have touched on just a few of the potential pitfalls you might encounter. Microsoft invested in the development of many resources to ensure a smooth upgrade path, including BOL, so be sure to check them out. BOL provides an excellent reference on compatibility issues and discusses many additional topics such as ODBC and operational (DBA) changes.

As with any project, before deploying your databases to production, be sure to thoroughly test your applications in the SQL Server 7.0 environment. If your developers have good SQL writing habits, you should not encounter many problems. However, many of the changes are behavioral in nature, so they won't show until execution time. In the end, most of the compatibility conflicts are a direct result of stronger ANSI compliance. Therefore, resolving those conflicts will result in a more robust, portable, and stable database. Make the time and do the migration. SQL Server 7.0 is worth upgrading to.