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