Microsoft KB Archive/133177

{|
 * width="100%"|

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

 * }

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:


 * 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
 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.</li> 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.</li>  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:

<pre class="CODESAMP">  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. </li> ## 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.</li> 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.</li>  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.

</li> 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.</li> 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.</li> 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.</li> 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.</li> 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.</li> 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.</li>  '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. </li> 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.</li>  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:

<pre class="CODESAMP">  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. </li> 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.</li> <li>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."</li> <li>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.</li> <li>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.</li> <li> 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 #." </li> <li> 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).

<pre class="CODESAMP">  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:

<pre class="FIXEDTEXT">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:

<pre class="FIXEDTEXT">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:

<pre class="FIXEDTEXT">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):

<pre class="FIXEDTEXT">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 </li></ol>

Additional query words: sql6 comparison reference apps

Keywords : kbenv kbother kbsetup

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600 kbSQLServ420OS2