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

Last reviewed: October 21, 1997
Article ID: Q133177

The information in this article applies to:
  • Microsoft SQL Server, versions 4.2x and 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:

  1. Default Sort Order and Default Character Set
2. System Table Changes 3. System Stored Procedure Changes and Changes in Format 4. BETWEEN Requires Low <= Hi 5. Changes in LIKE with Fixed-Length Character Values 6. Non-Aggregate Columns Must Appear in Group By 7. Nested Aggregates Not Allowed 8. > All and < All Now ANSI Compatible 9. Subqueries Now Conform to ANSI Standard 10. Use of Aliases 11. ## Tables and Stored Procedures Are Now Global 12. Column Nullability Is NULL by Default Rather Than NOT NULL 13. Stored Procedure Parameter Checking 14. Quotes When SET QUOTED_IDENTIFIER ON Is Set 15. Primary Keys and Foreign Keys 16. ODBC SQL Server Driver Has ArithAbort Turned On 17. User-Defined Datatype Names 18. Raiserror Error Numbers 19. Dumps Appended By Default 20. 'Conditional Compilation' No Longer Available 21. Extended Procedure Creation in Master Only 22. DATEDIFF Function Changes 23. Numeric Constants with Decimal are Numeric, Not Float 24. Extended Procedures Must be Recompiled and Re-Linked 25. Null Checks Are Runtime Now 26. Embedded Keywords 27. Temporary Tablename Lengths 28. Null from Nested Select Will Produce Row in Results Set (similar to #9)

Appendix: Reserved Words in SQL Server version 6.0

MORE INFORMATION

  1. 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".

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. Nested Aggregates Not Allowed

In SQL Server version 4.2x, the following nested aggregate was allowed:

   select count(count(*))
   from <table>

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.

  1. > 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.

  1. 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.

  1. Use of Aliases

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.

  1. ## 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.

  1. 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.

  1. 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.

    1. 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.

    1. 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.

    1. 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.

    1. 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.

    1. 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.

    1. '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.

    1. 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.

    1. 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.

    1. 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.

    1. 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."

    1. 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.

    1. 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.

    1. 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 #."

    1. 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 : SSrvGen SSrvInst kbenv kbother kbsetup
    Version : 4.2x 6.0
    Platform : WINDOWS
    Issue type : kbref


    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: October 21, 1997
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.