Backward Compatibility Details (Level 4)

Backward Compatibility Details (Level 4) consists of minor changes in Microsoft® SQL Server™ version 7.0 that produce different behavior from earlier versions of SQL Server. For example, items in this level are either ignored or have one or more ignored parameters, changes to byte lengths, added parameters or columns, or changed data type columns.

This topic covers backward compatibility details for these items.

This subheading	Relates to these items
Aliases	Roles Managing Permissions
Backup and Restore	RESTORE HEADERONLY LOAD HEADERONLY
Configuration	sp_configure (media retention option) Setting Configuration Options
CREATE PROCEDURE	CREATE TABLE SELECT INTO
Data Types	decimal and numeric Using Mathematical Functions + (Add) - (Subtract) * (Multiply) / (Divide) ATN2 AVG CAST and CONVERT EXP POWER RADIANS ROUND SUM
DATEPART and SET DATEFIRST	SET DATEFIRST DATEPART
DBCC	DBCC
DBCS String Comparisons	Unicode space characters
DELETE and SELECT	FROM
Devices	ALTER DATABASE
Functions	@@DBTS
Global Variables	Functions
ODBC	SQL_COPT_SS_PERF_QUERY_ INTERVAL SQLMoreResults SQL_NO_DATA
Rebuilding the master Database	Rebuild Master Utility
Rebuilding the Registry	setup /t regrebld Utility
Replication	Replication Between Different Versions of SQL Server Restricted Publications Subscribing to One or More Articles of a Publication
Security	SYSTEM_USER
SELECT	SELECT FROM
Triggers and System Stored Procedures (System)	CREATE TRIGGER sp_dboption (recursive triggers option) sp_tableoption xp_readmail xp_sendamil
UPDATE
Utilities	SQL Server Query Analyzer isql utility

Aliases (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
An alias allowed a user to temporarily assume the identity of another user within a database and perform actions as the aliased user. For example, the database owner could be aliased to a user so they could act as that user, if the user were on vacation.	Roles have replaced aliases. Because a user can belong to more than one role at a time, it is no longer necessary to assume the identity of another. Users belonging to the same roles have the same permissions automatically, assuming permissions are only applied at the role level, not the user level.	Expect different results as compared to earlier versions of SQL Server. Use roles instead of aliases. For more information about database roles, see Managing Permissions.

Backup and Restore (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
The LOAD HEADERONLY statement retrieved a result set detailing the header information from a database dump.	The result set returned for the LOAD HEADERONLY statement has changed.	Expect different results as compared to earlier versions of SQL Server. Expect a different result set from LOAD HEADERONLY, compared to pre-SQL Server 7.0 versions of LOAD HEADERONLY. For more information about LOAD HEADERONLY, see LOAD.

Configuration (Level 4)

Administration scripts may have used these configuration options. For more information about configuration options, see sp_configure and Setting Configuration Options.

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Administration scripts may have used the nonadvanced media retention option to set the number of days to retain each backup medium after it has been used for a database or transaction log dump.	media retention is now an advanced option.	Expect different results as compared to earlier versions of SQL Server. Expect the media retention configuration option to appear only if you have enabled the advanced configuration options of sp_configure.

CREATE PROCEDURE (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
CREATE PROCEDURE statements failed if they contained a CREATE TABLE or SELECT INTO statement creating a temporary table with the same name as a temporary table that existed at the time the CREATE PROCEDURE statement was executed.	The CREATE PROCEDURE statement succeeds.	Recode any logic that depended on the earlier behavior.

Data Types (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
The decimal and numeric data types could use anywhere from 2 through 17 bytes to store a value, depending on the precision of the stored value.	numeric and decimal now use 5, 9, 13, or 17 bytes of storage.	Expect different results as compared to earlier versions of SQL Server. Make sure that databases using numeric or decimal data types have sufficient storage for the change in storage bytes.
Results that were too small to display, called floating point underflow, returned inconsistent results for some mathematical operators and functions.	SQL Server now returns 0.0 and no error message for all instances of floating point underflow. Because of the fixed size of floating point numbers like the float and real data types, approximate numeric data have intrinsic precision and ranges of values. In cases of floating point underflow, a result of 0.0 will be returned and no error message will be displayed. For example, the mathematical calculation of 2 to the -100.0 power would have a result 0.0.	Expect different results as compared to earlier versions of SQL Server. Expect different results with floating point underflow with any of the Using Mathematical Functions or operators + (Add) - (Subtract) * (Multiply) / (Divide) ATN2 AVG CONVERT EXP POWER RADIANS SUM
A negative second parameter equal to the number of digits in the ROUND expression returned a value of 0 for integer, float, and money data types. When the second parameter was negative and less than the number of digits in the ROUND expression, ROUND returned a value that rounded the right-most digit down to 0.	When the second parameter in the ROUND function is negative for all data types, SQL Server returns a value rounded up to the next highest value, respecting decimal places, where appropriate.	Expect different results as compared to earlier versions of SQL Server. Expect different results with the ROUND function, compared to earlier versions of SQL Server, when the second parameter is negative.
The DATEADD and DATEDIFF functions returned a date value when adding or subtracting date values.	Direct date value addition and subtraction operations are supported for and datetime and smalldatetime using the +(Add) and -(Subtract) operators.	For simple date arithmetic, you can also use addition ( + (Add) ) or subtraction ( - (Subtract)) instead of DATEADD and DATEDIFF.

DATEPART and SET DATEFIRST (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
The SET DATEFIRST setting of the DATEPART function had no effect on the week datepart.	The week datepart may give values different from earlier versions of SQL Server. However, any difference will appear only if the SET DATEFIRST setting is not the default (the U.S. English default is 7, or Saturday). If the year provided in the DATEPART function has 366 days, a week value of 54 can be returned if the first week of the year starts on a Saturday, and the year ends on the same day of the week with the first day of the week counted from Sunday. When using the ISO 8601 standard, week values are always from 1 through 53, as the first week of the year is guaranteed to have a minimum of 4 days.	Expect different results as compared to earlier versions of SQL Server. Use the default value for SET DATEFIRST so that DATEPART returns the expected results for the week datepart. Otherwise, DATEPART values will be one less than expected.

Pre-SQL Server 7.0

SQL Server 7.0

Recommendations

The SET DATEFIRST setting of the DATEPART function had no effect on the week datepart.

The week datepart may give values different from earlier versions of SQL Server. However, any difference will appear only if the SET DATEFIRST setting is not the default (the U.S. English default is 7, or Saturday).

If the year provided in the DATEPART function has 366 days, a week value of 54 can be returned if the first week of the year starts on a Saturday, and the year ends on the same day of the week with the first day of the week counted from Sunday.

When using the ISO 8601 standard, week values are always from 1 through 53, as the first week of the year is guaranteed to have a minimum of 4 days.

Expect different results as compared to earlier versions of SQL Server. Use the default value for SET DATEFIRST so that DATEPART returns the expected results for the week datepart. Otherwise, DATEPART values will be one less than expected.

DBCC (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Each DBCC statement had a certain output format.	The output formats of many DBCC statements have changed.	Expect different results as compared to earlier versions of SQL Server. Expect changes in output from DBCC statements as compared to pre-SQL Server version 7.0 DBCC statements.
The DBCC PERFMON and DBCC SQLPERF statements documented SQL Server performance statistics used for studying SQL Server performance.	No longer documented. These statements may change in a future release of SQL Server.	Use the SQL Server Performance Monitor to monitor the performance counters for SQL Server. For more information, see Monitoring with Windows NT Performance Monitor.

DBCS String Comparisons (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
When comparing DBCS space characters, the Unicode A140 space character (U-A140) was not equal to the Unicode 0020 (U-0020) space character.	Comparisons involving the Unicode A140 space character (U-A140) are now equivalent to the Unicode 0020 (U-0020) space character.	Expect different results as compared to earlier versions of SQL Server when comparing DBCS space characters.

DELETE and SELECT (Level 4)

Pre-SQL Server 7.0 SQL Server 7.0 Recommendations

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Duplicate table names in the FROM clause of a SQL Server version 6.0 DELETE or SELECT statement caused SQL Server to treat both table references as the same table. SQL Server discarded the reference to the second authors table in this SELECT example: `USE pubs` `GO` `SELECT ` `FROM authors, authors` `GO` However, if the table names specified in the FROM clause of the DELETE or SELECT were not identical, SQL Server version 6.0 treated the two table references as two different tables as in this SELECT example: `USE pubs` `GO` `SELECT ` `FROM pubs..authors, pubs.dbo.authors` `GO`	Duplicate table names in the FROM clause of a DELETE or SELECT statement generated errors in SQL Server. Rewrite statements using aliases. Here is a SELECT example: `SELECT ` `FROM pubs..authors AS a1, pubs.dbo.authors AS a2` `USE pubs` `SELECT ` `FROM authors AS au1, authors AS au2`	Expect different results as compared to SQL Server version 6.0. Rewrite DELETE and SELECT statements to use aliases in the FROM clause when referring to more than one instance of the same table.

Duplicate table names in the FROM clause of a SQL Server version 6.0 DELETE or SELECT statement caused SQL Server to treat both table references as the same table. SQL Server discarded the reference to the second authors table in this SELECT example:

USE pubs

GO

SELECT *

FROM authors, authors

GO

However, if the table names specified in the FROM clause of the DELETE or SELECT were not identical, SQL Server version 6.0 treated the two table references as two different tables as in this SELECT example:

USE pubs

GO

SELECT *

FROM pubs..authors, pubs.dbo.authors

GO

Duplicate table names in the FROM clause of a DELETE or SELECT statement generated errors in SQL Server. Rewrite statements using aliases. Here is a SELECT example:

SELECT *

FROM pubs..authors AS a1, pubs.dbo.authors AS a2

USE pubs

SELECT *

FROM authors AS au1, authors AS au2

Expect different results as compared to SQL Server version 6.0. Rewrite DELETE and SELECT statements to use aliases in the FROM clause when referring to more than one instance of the same table.

Devices (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
The DISK RESIZE statement altered the size of a database device.	The DISK RESIZE statement is supported, but may not be supported in future releases. In addition, the DISK RESIZE statement does not alter the size of the database. Instead, use ALTER DATABASE.	Expect different results as compared to earlier versions of SQL Server. Use the MODIFY file_decl clause of the ALTER DATABASE statement to alter the size of a database.

Functions (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
The @@DBTS global variable was incremented any time any page in the database was modified in any way.	The value returned by @@DBTS changes only if a row containing a timestamp column is modified.	Expect different results as compared to earlier versions of SQL Server when using @@DBTS.

Global Variables (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Global variables were those system-supplied, predeclared variables that were distinguished from local variables by having two at symbols (@@) preceding their names.	Transact-SQL global variables are a form of function and are now referred to as functions.	Expect different results as compared to earlier versions of SQL Server. For more information, see Functions.

ODBC (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
In the version 2.65 ODBC driver, the long-running query interval, specified by calling SQLSetConnectOption with the driver-specific connection option SQL_COPT_SS_PERF _QUERY_INTERVAL, was specified in seconds.	The SQL_COPT_SS_PERF _QUERY_INTERVAL value is specified in milliseconds.	Expect different results as compared to earlier versions of SQL Server. Multiply the value of SQL_COPT_SS_PERF _QUERY_INTERVAL by 1,000 to convert the number of seconds to milliseconds. For more information about SQL_COPT_SS_PERF _QUERY_INTERVAL, see SQLSetConnectAttr.
For earlier versions of the ODBC SQL Server driver, messages from consecutive PRINT, RAISERROR, DBCC or similar statements (in a batch or stored procedure) were combined into a single result set.	For the ODBC SQL Server 3.5 driver (included with SQL Server 7.0), messages from consecutive PRINT, RAISERROR, DBCC or similar statements (in a batch or stored procedure) are returned in a separate result set for each statement.	Expect different results as compared to earlier versions of SQL Server. Call SQLMoreResults to process the result set from each statement.
Earlier versions of the ODBC SQL Server driver returned SQL_SUCCESS when executing a searched UPDATE or DELETE statement that affects no rows (using SQLExecute, SQLExecDirect, or SQLParamData). SQLRowCount returned zero.	When an ODBC version 3.x application uses the ODBC SQL Server 3.5 driver included with this release, it returns SQL_NO_DATA when executing a searched UPDATE or DELETE statement that affects no rows (using SQLExecute, SQLExecDirect, or SQLParamData). SQLRowCount still returns zero.	Expect different results as compared to earlier versions of SQL Server. Handle SQL_NO_DATA appropriately.

Rebuilding the master Database (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Executing SQL Server Setup rebuilt the master database.	No longer supported. SQL Server includes the Rebuild Master (rebuildm) utility.	Use the Rebuild Master (rebuildm) Utility located in the \Mssql7\Binn folder to rebuild the master database.

Rebuilding the Registry (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Executing this SQL Server Setup rebuilt the registry: `setup/t RegistryRebuild = On.`	No longer supported. SQL Server includes the regrebld utility for backing up and restoring the registry.	Use the regrebld utility located in the \Mssql7\Binn folder to rebuild the registry.

Replication (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Restricted publications could be created through SQL Server Enterprise Manager and used in replicating data.	No longer supported.	Expect different results as compared to earlier versions of SQL Server. Remove all references to restricted publications. A replacement for restricted publications will be available in a later release.
Subscriptions to one or more articles in a publication were created either through SQL Server Enterprise Manager or through the appropriate system stored procedures.	SQL Server Enterprise Manager no longer allows subscription to one or more articles. Subscribing to one or more articles of a publication can be done only by using the appropriate replication system stored procedures.	Expect different results as compared to earlier versions of SQL Server. Use the replication system stored procedures to subscribe to one or more articles of a publication. When using SQL Server Enterprise Manager, it is necessary to subscribe to the entire publication.

Replication functions differently between Microsoft® SQL Server™ 7.0 and SQL Server 6.5 servers. In addition, SQL Server 7.0 offers enhanced scripting ability after your replication topology is created in the user interface. This enhanced scripting allows mass implementation of replication topology with a minimum of time and effort. For more information, see Replicating Between Different Versions of SQL Server.

Security (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
The SYSTEM_USER niladic function returned nulls for any Microsoft Windows NT® login.	The appropriate Windows NT domain and login names are returned if Windows NT Authentication is used with the SYSTEM_USER function.	Expect different results as compared to earlier versions of SQL Server. Expect a different result, as compared to earlier versions, when using SYSTEM_USER with Windows NT Authentication.

SELECT (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
The = and = operators (left outer join and right outer join, respectively) were used in SELECT statements to produce left and right outer joins, respectively.	The SQL-92-standard syntax of LEFT OUTER JOIN and RIGHT OUTER JOIN is preferred. However, pre-SQL Server 7.0 join operators are supported.	Expect different results as compared to earlier versions of SQL Server. Remove all references of the = (left outer join) and = (right outer join) operators in all SELECT statement FROM clauses and replace with references to the SQL-92-standard syntax RIGHT OUTER JOIN and LEFT OUTER JOIN. Future versions of SQL Server will support only the SQL-92-standard syntax.

Triggers and System Stored Procedures (System) (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
Returned values were not always correct for text or image columns in either the inserted or deleted tables when either table was used in a CREATE TRIGGER statement.	NULL values are returned for text or image column references in the inserted or deleted tables in CREATE TRIGGER.	Expect different results as compared to earlier versions of SQL Server. Remove or comment out all references to either the text or image columns of the inserted or deleted tables when used in CREATE TRIGGER statements.
Direct recursion of triggers (the ability of a trigger to call itself) was not supported, but indirect recursion was allowed.	Direct trigger recursion is supported with the recursive triggers setting of sp_dboption.	Expect different results as compared to earlier versions of SQL Server. Enable the recursive triggers setting of sp_dboption to use direct trigger recursion. For more information about the recursive triggers setting see Setting Database Options.
Server-to-server communication existed between version 4.x and version 6.x servers when initialized by either side.	Version 4.x or 6.x servers can communicate with SQL Server 7.0 servers. However, server-to-server communication is not supported from SQL Server 7.0 servers to 4.x servers.	Expect different results as compared to earlier versions of SQL Server. Upgrade the 4.x server to either SQL Server 6.x or SQL Server 7.0.
sp_tableoption set option values for user-defined tables, including the use of Insert Row-level locking (IRL).	The Insert Row-level locking (IRL) parameters in sp_tableoption are not supported but have been replaced with complete row-level locking.	Expect different results as compared to earlier versions of SQL Server. Remove all references to IRL actions implemented using sp_tableoption and use the built-in row-level locking of SQL Server 7.0 instead. Applications calling sp_tableoption should continue to work properly; the IRL parameters will be ignored.
The @message parameter of xp_readmail was varchar(255). The @message and @query parameters of xp_sendmail were varchar(255).	The @message and @query parameters are now varchar(8000).	Expect differences in behavior as compared to earlier versions of SQL Server when using the @message parameter of xp_readmailand the @message and @query parameters of xp_sendmail.

UPDATE (Level 4)

Pre-SQL Server 7.0 SQL Server 7.0 Recommendations

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
In SQL Server version 6.0, the following UPDATE statement, using two different table aliases for the same base table, was allowed: `CREATE TABLE t1 (c1 int) GO INSERT t1 VALUES (1) INSERT t1 VALUES (2) GO UPDATE t1 SET c1 = 50 FROM t1 a1, t1 a2 WHERE a1.c1 = 1 AND a2.c1 = 2 GO`	No longer supported. Use the table alias following the UPDATE keyword of the UPDATE statement rather than the table name. The UPDATE statement would be rewritten to: `UPDATE a1 SET c1 = 50 FROM t1 a1, t1 a2 WHERE a1.c1 = 1 AND a2.c1 = 2`	Expect differences in behavior as compared to SQL Server version 6.0 when referring to a table by its table name rather than a table alias following the UPDATE keyword of the UPDATE statement.
This UPDATE statement with table and alias references worked. USE pubs `GO` `UPDATE titles` `SET t.ytd_sales = t.ytd_sales + s.qty` `FROM titles t, sales s` `WHERE t.title_id = s.title_id` `AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)` `GO`	The alias specified after the UPDATE keyword must match the alias specified following the SET keyword. Without this change, the compatibility level setting must be changed to 65 for this UPDATE statement to function as it did in version 6.x. Here is the same UPDATE statement rewritten: `USE pubs` `GO` `UPDATE t` `SET t.ytd_sales = t.ytd_sales + s.qty` `FROM titles t, sales s` `WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)` `GO`	Expect differences in behavior as compared to SQL Server version 6.x when using different table references following the UPDATE keyword of the UPDATE statement and the SET keyword of the UPDATE statement.

In SQL Server version 6.0, the following UPDATE statement, using two different table aliases for the same base table, was allowed:

CREATE TABLE t1 (c1 int) GO INSERT t1 VALUES (1) INSERT t1 VALUES (2) GO UPDATE t1 SET c1 = 50 FROM t1 a1, t1 a2 WHERE a1.c1 = 1 AND a2.c1 = 2 GO

No longer supported. Use the table alias following the UPDATE keyword of the UPDATE statement rather than the table name. The UPDATE statement would be rewritten to:

UPDATE a1 SET c1 = 50 FROM t1 a1, t1 a2 WHERE a1.c1 = 1 AND a2.c1 = 2

Expect differences in behavior as compared to SQL Server version 6.0 when referring to a table by its table name rather than a table alias following the UPDATE keyword of the UPDATE statement.

This UPDATE statement with table and alias references worked.
USE pubs

GO

UPDATE titles

SET t.ytd_sales = t.ytd_sales + s.qty

FROM titles t, sales s

WHERE t.title_id = s.title_id

AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

GO

The alias specified after the UPDATE keyword must match the alias specified following the SET keyword. Without this change, the compatibility level setting must be changed to 65 for this UPDATE statement to function as it did in version 6.x.

Here is the same UPDATE statement rewritten:

USE pubs

GO

UPDATE t

SET t.ytd_sales = t.ytd_sales + s.qty

FROM titles t, sales s

WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

GO

Expect differences in behavior as compared to SQL Server version 6.x when using different table references following the UPDATE keyword of the UPDATE statement and the SET keyword of the UPDATE statement.

Utilities (Level 4)

Pre-SQL Server 7.0	SQL Server 7.0	Recommendations
isql/w used DB-Library. The SQL-92 settings, like ANSI_WARNINGS, were set off, by default.	SQL Server Query Analyzer uses the SQL Server ODBC driver, which, by default, sets these SQL-92 options on: SET ANSI_WARNINGS, SET ANSI_PADDING, and SET ANSI_NULLS. Any errors returned are formatted as ODBC errors rather than DB-Library errors.	Expect different results as compared to earlier versions of SQL Server. Expect different results with SQL Server Query Analyzer, compared to the isql utility or the SQL Server 6.5 version of isql/w. These SQL-92 options can also be turned off on the Query Current Connections Options menu.
isql/w used the ANSI-ISO code pages. When connected to a server using OEM code page 850 or 437, ANSI to OEM character translation had to be explicitly enabled. Otherwise, data with extended characters appeared garbled.	The SQL Server 7.0 ODBC driver automatically detects the need for and sets up automatic ANSI to OEM conversion. In addition, when SQL Server Query Analyzer connects, automatic detection is enabled.	Expect different results as compared to earlier versions of SQL Server. Expect a change in behavior when using SQL Server Query Analyzer with international or extended characters.