INF: Changes to SQL Server 6.0 That May Affect 4.2x Apps

ID: Q133177

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0

SUMMARY

This article contains a list of changes made to Microsoft SQL Server version 6.0 that may affect your existing version 4.21 applications.

Contents:

Default Sort Order and Default Character Set

System Table Changes

System Stored Procedure Changes and Changes in Format

BETWEEN Requires Low <= Hi

Changes in LIKE with Fixed-Length Character Values

Non-Aggregate Columns Must Appear in Group By

Nested Aggregates Not Allowed

> All and < All Now ANSI Compatible

Subqueries Now Conform to ANSI Standard

Use of Aliases

## Tables and Stored Procedures Are Now Global

Column Nullability Is NULL by Default Rather Than NOT NULL

Stored Procedure Parameter Checking

Quotes When SET QUOTED_IDENTIFIER ON Is Set

Primary Keys and Foreign Keys

ODBC SQL Server Driver Has ArithAbort Turned On

User-Defined Datatype Names

Raiserror Error Numbers

Dumps Appended By Default

'Conditional Compilation' No Longer Available

Extended Procedure Creation in Master Only

DATEDIFF Function Changes

Numeric Constants with Decimal are Numeric, Not Float

Extended Procedures Must be Recompiled and Re-Linked

Null Checks Are Runtime Now

Embedded Keywords

Temporary Tablename Lengths

Null from Nested Select Will Produce Row in Results Set (similar to #9)

Appendix: Reserved Words in SQL Server version 6.0

MORE INFORMATION

Default Sort Order and Default Character Set

For SQL Server version 6.0, the default sort order is "Dictionary order, case-insensitive" and the default character set is "ISO 8859-1 (Latin 1 or ANSI)". In SQL Server version 4.2x, the default sort order is "Binary order" and the default character set is "850 Multilingual".

System Table Changes

There have been several changes to system tables: new columns have been added, the meaning or contents of some columns has been changed, and some new system tables have been added.

System Stored Procedure Changes and Changes in Format

There have been several changes to system stored procedures. In many cases the functionality has been expanded to include new SQL Server version 6.0 features.

The function of some stored procedures has been integrated into the server itself, as in the case of sp_primarykey, and so forth. In some cases, the format of the output has been modified to provide a clearer presentation of the information.

BETWEEN Requires Low <= Hi

When using the "z BETWEEN x AND y" comparison, x must now be less than or equal to y to return any matching values of z. To be ANSI compliant, it now acts as "z >= x AND z <= y" whereas previously it would effectively swap x and y if x was larger before performing the comparison.

Changes in LIKE with Fixed-Length Character Values

The LIKE operator no longer treats trailing blanks in the pattern as insignificant. Trailing blanks are significant and must match the source. The former behavior for fixed length character was not ANSI standard. Although the declaration of datatype was exact and fixed, the LIKE operator wasn't considering it as such. ANSI's semantics for LIKE are different from "="; they are 2 different operators, each with their own semantics. The former functionality was not transferable to any other database. Trace flag 204 can be used to revert to the previous behavior, but affects other enhancements as well.

NOTE: The LIKE operator not treating trailing blanks as insignificant, refers to the LIKE pattern, not to the expression to which the LIKE pattern is matched (which is often a column). Hence, the clause WHERE lname LIKE 'smith ' (three trailing spaces) would not match a CHAR(7) not null column containing the value 'smith ' (two trailing spaces). However WHERE lname like 'smith ' (one trailing space) would match a CHAR(7) not null column containing the value 'smith ' (two trailing spaces). For more details see the "Search Conditions" section of the Transact-SQL Reference.

Non-Aggregate Columns Must Appear in GROUP BY

In earlier releases of SQL Server, it was possible to have non-aggregate columns in the SELECT list or columns in the SELECT list that were not also listed in the GROUP BY clause. This often produced undesired results. For ANSI compatibility, SQL Server version 6.0 does not allow non-aggregate columns in the SELECT list or columns in the SELECT list that are not also listed in the GROUP BY clause. Trace flag 204 can be used to revert to the previous behavior, but affects other enhancements as well.

Nested Aggregates Not Allowed

In SQL Server version 4.2x, the following nested aggregate was allowed:
```
   select count(count(*))
   from &lt;table&gt; 
```
In SQL Server version 6.0, an expression passed to an aggregate function cannot contain an aggregate or a subquery. The following error message is given:

Msg 130, Level 15, State 1 Cannot perform an aggregate function on an
expression containing an aggregate or a subquery.

Trace flag 204 can be used to revert to the previous behavior, but affects other enhancements as well.

> All and < All

Now ANSI Compatible Queries with >ALL (greater than ALL) or <ALL (less than ALL) should evaluate to true when the subquery returns no rows. In earlier releases, this situation evaluated to false (and subsequently no rows were returned). ANSI compatibility has been implemented to cause this condition to return all rows that meet the conditions of the outer query.

Subqueries Now Conform to ANSI Standard

In addition to performance enhancements to subquery processing, SQL Server version 6.0 has many ANSI-compatibility changes. Because of these changes, it is important that you test all subqueries to ensure desired results in your applications. Subqueries that define the scope of a NOT IN WHERE clause should evaluate to UNKNOWN or FALSE for each row that returns a NULL. In earlier versions of SQL Server, rows with NULL entries in the evaluated column were returned as part of the NOT IN results set.

For ANSI compatibility, SQL Server version 6.0 returns only those rows with valid data that is not in the subquery search condition. Subqueries in the WHERE clause were previously evaluated such that an EXISTS, IN, or ANY, when zero rows met the request, caused the entire query to return zero rows even if the outer query used an OR for the conditions. In SQL Server version 6.0, each condition is evaluated separately to provide accurate results when one or more condition(s) return no results. In earlier releases, in correlated subqueries where duplicates exist, a query that includes aggregates and EXISTS subqueries sometimes evaluated the query incorrectly. In SQL Server version 6.0, these queries are resolved because the subquery is evaluated first.

In earlier releases, evaluating a SELECT DISTINCT correlated subquery within an IN condition of the outer query would cause the outer query to return no rows. In SQL Server version 6.0, this has been resolved so that DISTINCT values are returned to the outer query for evaluation. Correlated subqueries that reference the same table as the outer query need to alias and properly identify each occurrence of that table. If an alias is used in the FROM clause, it should also be used in the WHERE clause and, if referenced, then also aliased correctly in the subquery.

If you have aliased the table, you must use the alias throughout the query in place of the table name. The following query worked in version 4.21a and does not work in version 6.0:
```
   select authors.au_lname
   from authors auth 
```
SQL Server version 6.0 gives the error message:

The column prefix 'authors' does not match with a table name or alias name used in the query.

This syntax checking was tightened up as the optimizer was improved.

## Tables and Stored Procedures Are Now Global

Any table name or stored procedure name that begins with ## (two number signs) now indicates that the table or stored procedure should be accessible globally. In version 4.2x, a table or stored procedure named ##test, for example, was available only to the creating connection, which remains the case in version 6.0 for names beginning with one number sign.

Column Nullability depends on database and session settings.

The default nullability of a column now depends on session settings. In version 4.2x, you declared a column to be NULL or NOT NULL and if it was not specified, the default was NOT NULL. You explicitly had to allow a column to be set to NULL. In version 6.0, the server-wide default is still NOT NULL but this can be overridden at the database level and at the session level.

The ANSI standard default is NULL; it is recommended that you always specify NULL or NOT NULL when creating the column rather than depending on the database or session settings. You can set the database default by using the sp_dboption 'ANSI null default' and the session level default by using the SET statement to set ANSI_Null_Dflt_On or ANSI_Null_Dflt_Off.

Stored Procedure Parameter Checking

SQL Server version 6.0 now checks stored procedure calls for too many parameters, or the use of named parameters that were not on the parameter list. For example, sp_help takes an @objname parameter; calling sp_help @obj = 'SomeName' SQL Server version 4.2x did not fill @objname and operated as if no parameters were specified, and did not give an error. SQL Server version 6.0 detects the parameter name mismatch and issues the following error:

Msg 8145, Level 16, State 2 @obj is not a parameter for procedure sp_help.

Quotes When SET QUOTED_IDENTIFIER ON Is Set

When SET QUOTED_IDENTIFIER ON has been given, double quotes (") can only be used to delimit identifiers; they cannot be used to delimit strings. ANSI specifies that single quotes delimit strings and double quotes are only used to avoid problems with reserved words. SQL Server version 6.0 by default assumes a single quote or a double quote begins and ends a string, as does version 4.2x. However, once the SET option has been specified, version 6.0 assumes double quotes are used only as delimiters for identifiers; strings must be delimited by single quotes.

Primary Keys and Foreign Keys

The stored procedures sp_primarykey, sp_commonkey, sp_dropkey, sp_foreignkey, sp_helpjoins, sp_helpkey, and the syskeys table are included for backward compatibility only. These procedures (and the syskeys table) are related to old style primary and foreign keys that do not implement referential integrity. To utilize SQL Server version 6.0 declarative referential integrity (DRI), use the ALTER TABLE and the CREATE TABLE statements. Use the sp_help or the sp_helpconstraint system stored procedures to view constraint information. Note that sp_pkeys and sp_fkeys still return valid information, however that information is now based on the true declarative referential integrity from the CREATE and ALTER TABLE statements.

ODBC SQL Server Driver Has ArithAbort Turned On

The ODBC SQL Server driver has arithabort turned on. This is designed to be consistent with the ODBC standard that states that numeric overflows and divide by zero errors are fatal. This may cause a change in some ODBC tests because they may have been coded to the previous default behavior.

User-Defined Datatype Names

In version 4.2x, it is possible to create a user-defined datatype named INT which in version 6.0 is taken to mean the same as 'int'. The case of a datatype's name is no longer relevant. Integer and Character are now alternative forms of int and char.

Raiserror Error Numbers

In version 4.2x, the user error numbers started at 20,000; in SQL Server version 6.0, error numbers up to 50,000 are reserved.

Dumps Appended By Default

In version 4.2x, dumping to a device that already contained a dump would overwrite the existing data with the newer information; in SQL Server version 6.0, the newer information is appended by default; the user must use INIT to overwrite an existing dump. Users may discover that their dump devices fill up now where they never did before.

'Conditional Compilation' No Longer Available

In a stored procedure using transact-SQL IF statements for conditional execution based on parameters passed, under some conditions a "not taken" IF block will now cause compilation to fail. This is so even if the statement in the IF block causing the error should not be checked until run time (such as a convert).

This did not happen on version 4.21x. The error in this scenario was:

Failure msg: Msg 249, Level 16, State 1
Syntax error converting CHAR value 'sysobjects' to an INT4 field.

A change from version 4.2x to 6.0 causes compile-time evaluation of constant expressions in some cases where it was not performed previously. Compile-time evaluation has the performance advantage of allowing the optimizer to know SP parameters. In general, the version 4.2x server has always done this, version 6.0 now does it in a few more cases. As a side- effect, this gives errors for SPs which rely on the cases where compile- time evaluation was not done in 4.2x, but is now in version 6.0.

Extended Procedure Creation in Master Only

With SQL Server version 6.0 extended stored procedures (XPs) can now only be created in the master database (DB), not in user DBs as you could previously.

DATEDIFF Function Changes

This example shows the difference in results of the new DATEDIFF function in SQL Server version 6.0. The difference is minor; however, it accurately reflects the number of "minute boundaries" that were crossed. For the function call:
```
   SELECT DATEDIFF(minute, 'jan 1 1995 22:30:16', 'jan 1 1995 23:30:15') 
```
In earlier releases the number of "minute boundaries" returned by this query was 59. In SQL Server version 6.0, the number of "minute boundaries" crossed is 60.

Numeric Constants with Decimal are Numeric, Not Float

In SQL Server version 4.2x, a numeric constant containing a decimal point was represented by a Float value. In SQL Server version 6.0, numeric constants containing a decimal point are represented by a Numeric value.

Extended Procedures Must be Recompiled and Relinked

You have to recompile and link with the OPENDS60.LIB file to use your extended stored procedure with SQL Server version 6.0. See the Microsoft SQL Server version 6.0 Books Online, "Programming Open Data Services," Chapter 2, System Requirements, "Existing Open Data Services applications must be recompiled and rebuilt using Open Data Services 6.0."

Null Checks Are Runtime Now

Checks for the assignment of NULL to a NOT NULL column are made at run time rather than at compile time whenever possible as in version 4.2x. A violation results in a command abort thus backing out the INSERT or UPDATE that caused the violation but continuing with the batch or transaction. NULL assignment violations behave the same as an equivalent constraint violation. Trace flag "243" reverts to the old version 4.2x behavior.

Embedded Keywords

To implement many of the new features of SQL Server 6.0, as well as to be more ANSI compliant, many new words have been added to the list of keywords reserved by SQL Server (see the lists below). The CHKUPG.EXE utility detects the use of new reserved keywords in the existing database, but it cannot detect those embedded in applications or other scripts. So even if the database gets an "okay" from CHKUPG.EXE, there is still the possibility that an application could submit a query using a reserved keyword, such as SELECT KEY FROM TABLE, and so forth. These keywords should either be changed to a non-keyword, or they should be double-quoted (a 'quoted identifier) and used with the 'SET QUOTED IDENTIFIER ON' session option.

Temporary Tablename Lengths

In SQL Server version 6.0, you may now code up to 20 characters for a temporary #tablename; all 20 characters will be used. If more than 20 characters are given, you will get this error message:

Msg 193, Level 15, State 1 The object or column name starting with
'#23456789_123456789_1' is too long. The maximum length is 20
characters.

In SQL Server version 4.21a, you could code a temporary #tablename longer than 13 characters, but only the first 13 would be used (the remainder of the 30 bytes would be used for a system generated string). The version 4.21a "Transact-SQL Reference" manual, under the section entitled "Temporary Tables", states "The name must not exceed 13 characters, including the initial #."

Null from Nested Select Will Produce Row in Results Set (similar to #9)

In SQL Server version 6.0, the following two queries return identical results, with the inner select in the first select performing similarly to the outer join in the second select.

In SQL Server version 4.21a, the first select returns fewer rows, omitting those rows in authors where the inner select returned a null. This is also true on singleton selects (without the 'max' which was added because the inner select in this example may return more than one row).


   use pubs
   go
   select au_id,
     (select max(title_id)
      from titleauthor ta
      where ta.au_id = au.au_id)
   from authors au
   go
   select au.au_id, max(ta.title_id)
   from authors au, titleauthor ta
   where au.au_id *= ta.au_id
   group by au.au_id
   go

APPENDIX: RESERVED WORDS IN SQL SERVER VERSION 6.0

Reserved words checked by the CHKUPG.EXE utility:


ABSOLUTE          DEFERRED        LEVEL         SCHEMA
ACTION            DESCRIBE        LOCAL         SCROLL
ADD               DESCRIPTOR      MATCH         SECOND
ALLOCATE          DIAGNOSTICS     MINUTE        SERIALIZABLE
ARE               DISCONNECT      MONTH         SESSION
ASSERTION         DOMAIN          NAMES         SESSION_USER
AT                DOUBLE          NATIONAL      SIZE
AUTHORIZATION     END_EXEC        NATURAL       SOME
BOTH              ESCAPE          NCHAR         SPACE
CASCADE           EXCEPTION       NEXT          SQLSTATE
CASCADED          EXPIREDATE      NO            SUM
CASE              EXTERNAL        NOCHECK       SYSTEM_USER
CAST              EXTRACT         NULLIF        THEN
CATALOG           FALSE           OCTET_LENGTH  TIME
CHAR_LENGTH       FETCH           OF            TIMESTAMP
CHARACTER         FILE            ONLY          TIMEZONE_HOUR
CHARACTER_LENGTH  FIRST           OPEN          TIMEZONE_MINUTE
CLOSE             FLOPPY          OPTION        TRAILING
COALESCE          FOREIGN         OUTER         TRANSLATE
COLLATE           FULL            OUTPUT        TRANSLATION
COLLATION         GET             OVERLAPS      TRUE
COLUMN            GLOBAL          PAD           UNCOMMITTED
CONNECT           HOUR            PARTIAL       UNKNOWN
CONNECTION        IDENTITY        PIPE          UPDATETEXT
CONSTRAINT        IDENTITY_INSERT POSITION      USAGE
CONSTRAINTS       IDENTITYCOL     PRECISION     USER
CORRESPONDING     IMMEDIATE       PRESERVE      USING
CROSS             INITIALLY       PRIMARY       VALUE
CURRENT           INNER           PRIOR         VARYING
CURRENT_DATE      INPUT           PRIVILEGES    VOLUME
CURRENT_TIME      INSENSITIVE     READ          WHEN
CURRENT_TIMESTAMP INTERVAL        REFERENCES    WORK
CURRENT_USER      ISOLATION       RELATIVE      WRITE
CURSOR            JOIN            REPLICATION   YEAR
DATE              KEY             RESTRICT      ZONE
DAY               LAST            RETAINDAYS
DEALLOCATE        LEADING         RIGHT
DEFERRABLE        LEFT            ROWS

(The following lists have not been cross-checked with the preceding list. See SQL Server version 6.0 Books Online under 'Reserved Words' and 'Keywords' for additional lists.)

New words reserved for use by SQL Server version 6.0:


ADD               FETCH             NULLIF       SCROLL
CLOSE             FLOPPY            OF           SERIALIZABLE
COALESCE          FOREIGN           ONLY         SESSION_USER
CONSTRAINT        IDENTITY          OPEN         SOME
CURRENT           IDENTITY_INSERT   OPTION       SYSTEM_USER
CURRENT_DATE      IDENTITYCOL       PIPE         THEN
CURRENT_TIME      INSENSITIVE       PRIMARY      UPDATETEXT
CURRENT_TIMESTAMP ISOLATION         READ         USER
CURRENT_USER      KEY               REFERENCES   VARYING
CURSOR            LEVEL             REPEATABLE   UNCOMMITTED
DEALLOCATE        NOCHECK           REPLICATION

Words reserved for future use:


ACTION            DEFERRED         IMMEDIATE     PRIVILEGES
ASSERTION         ESCAPE           INITIALLY     RETAINDAYS
CASCADE           EXPIREDATE       MATCH         SCHEMA
CHARACTER         FILE             NATIONAL      VOLUME
DEFERRABLE        FULL             PARTIAL       WORK

All words reserved for SQL Server 6.0 (current and future combined):


ACTION            DESC             LEVEL         REVOKE
ADD               DISK             LIKE          ROLLBACK
ALL               DISTINCT         LINENO        ROWCOUNT
ALTER             DOUBLE           LOAD          RULE
AND               DROP             MATCH         SAVE
ANY               DUMMY            MAX           SCHEMA
AS                DUMP             MIN           SCROLL
ASC               ELSE             MIRROREXIT    SELECT
ASSERTION         END              NATIONAL      SERIALIZABLE
AVG               ERRLVL           NOCHECK       SESSION_USER
BEGIN             ERROREXIT        NONCLUSTERED  SET
BETWEEN           ESCAPE           NOT           SETUSER
BREAK             EXCEPT           NULL          SHUTDOWN
BROWSE            EXEC             NULLIF        SOME
BULK              EXECUTE          OF            STATISTICS
BY                EXISTS           OFF           SUM
CASCADE           EXIT             OFFSETS       SYSTEM_USER
CASE              EXPIREDATE       ON            TABLE
CHECK             FETCH            ONCE          TAPE
CHECKPOINT        FILE             ONLY          TEMP
CLOSE             FILLFACTOR       OPEN          TEMPORARY
CLUSTERED         FLOPPY           OPTION        TEXTSIZE
COALESCE          FOR              OR            THEN
COMMIT            FOREIGN          ORDER         TO
COMMITTED         FROM             OVER          TRAN
COMPUTE           FULL             PARTIAL       TRANSACTION
CONFIRM           GOTO             PERM          TRIGGER
CONSTRAINT        GRANT            PERMANENT     TRUNCATE
CONTINUE          GROUP            PIPE          TSEQUAL
CONTROLROW        HAVING           PLAN          UNCOMMITTED
CONVERT           HOLDLOCK         PRECISION     UNION
COUNT             IDENTITY         PREPARE       UNIQUE
CREATE            IDENTITY_INSERT  PRIMARY       UPDATE
CURRENT           IDENTITYCOL      PRINT         UPDATETEXT
CURRENT_DATE      IF               PRIVILEGES    USE
CURRENT_TIME      IMMEDIATE        PROC          USER
CURRENT_TIMESTAMP IN               PROCEDURE     VALUES
CURRENT_USER      INDEX            PROCESSEXIT   VARYING
CURSOR            INITIALLY        PUBLIC        VIEW
DATABASE          INSENSITIVE      RAISERROR     VOLUME
DBCC              INSERT           READ          WAITFOR
DEALLOCATE        INTERSECT        RECONFIGURE   WHEN
DECLARE           INTO             REFERENCES    WHERE
DEFAULT

Additional query words: sql6 comparison reference apps


Keywords          : kbenv kbother kbsetup SSrvGen SSrvInst 
Version           : 4.2x 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 14, 1999