1. System Table Changes
There have been some changes to system tables: new columns have been
added, the meaning or contents of some columns have been changed, and some
new system tables have been added. For more information, see SQL Server 6.5
Books Online, What's New for SQL Server 6.5, Part 4: "What's New for
2. 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.5
features. In some cases, the format of the output has been modified to
provide a clearer presentation of the information. For more information,
see SQL Server 6.5 Books Online, What's New for SQL Server 6.5, Part 4:
"What's New for Transact-SQL."
3. New Installation Defaults
The default root directory on new installations is MSSQL rather than
SQL60 (as in 6.0) or SQL (as in 4.2x). When you upgrade a 4.2x or 6.0
installation, the existing directory name is preserved.
The following words, reserved in SQL Server 6.0, are keywords in SQL Server
AUTHORIZATION FULL OUTER SCHEMA
CASCADE INNER PRIVILEGES WORK
CROSS JOIN RESTRICT
ESCAPE LEFT RIGHT
The word DISTRIBUTED is also a new keyword in SQL Server 6.5.
All database objects are automatically checked for conflicts with these new
keywords by running ChkUpg65.exe.
5. FROM Clause and Table Names
In SQL Server 6.5, errors are reported when redundant table names appear in
the FROM clause. For example, the SELECT statements given below were
supported in earlier releases but generate errors in SQL Server 6.5. In the
first SELECT statement, the tables were treated as two different tables. In
the second SELECT statement the second author's reference is discarded.
SELECT * FROM pubs..authors, pubs.dbo.authors
SELECT * FROM authors, authors
Previously, SQL Server used string comparisons alone to determine whether
two table names identified the same table. For example, pubs.dbo.authors
and pubs..authors were considered to be different tables. Now if two table
names are not identical, the database IDs and table IDs are compared to
determine whether or not they are the same table. Previously in an Update
statement SQL Server would simply find the first table in the FROM clause
that matched an unqualified column name and assume that was the table the
user meant. Now this type of query will cause an error, because it is
uncertain which table should be updated. Trace flag 110 will disable all of
6. SELECT DISTINCT with ORDER BY
Previous versions of SQL Server allowed SELECT DISTINCT queries containing
sort columns in the ORDER BY clause that were not in the select list. For
SELECT DISTINCT au_id FROM authors ORDER BY au_lname
SQL Server 6.5 complies with the ANSI Standard, resulting in error 145:
Order-by items must appear in the select-list if SELECT DISTINCT is
Trace flag 204 enables the old, non-ANSI behavior (as well as other non-
ANSI behavior involving subqueries and so forth from SQL Server 6.0).
7. REFERENCES Permission Required to Create Foreign Key
In SQL Server 6.5, if you create a foreign key on a table that you do
not own, you must have REFERENCES permission on the table; this complies
with the ANSI standard. In SQL Server 6.0, only SELECT permission was
required on the referenced table. Trace flag 237 enables the old behavior.
8. SELECT INTO or CREATE VIEW without Column Name
In SQL Server version 6.5, an error occurs if no column name is given to a
column created by a SELECT INTO or CREATE VIEW statement. For example,
CREATE VIEW testview AS SELECT au_id, upper(au_lname) FROM authors
results in error message 4511:
Create view failed because no column name was specified for column 2.
A column alias should be specified for the second column.
SQL Server 6.0 allowed this; trace flag 246 enables the old behavior.
9. RAISERROR Sets @@ERROR to Zero if Severity is Ten or Less
The RAISERROR statement now sets @@ERROR to zero if the severity is between
one and ten inclusive (messages with severity levels ten and under are not
errors, but they do provide additional information). If you set the msg_id
by using the WITH SETERROR option, the RAISERROR statement assigns the
msg_id to @@ERROR regardless of severity.
In SQL Server version 6.0, @@ERROR is set to 50,000 for messages with
severity levels ten and under.
To revert to SQL Server version 6.0 behavior, use either the SETERROR
option or trace flag 2701.
10. Startup Procedure: sp_sqlregister
In SQL Server 6.5 the stored procedure sp_sqlregister is installed as a
default startup procedure. At startup, sp_sqlregister gathers basic
configuration information from the operating system, network, and SQL
Server, and then broadcasts the SQL Server's presence on the network. Any
server carrying out xp_sqlinventory can collect the information into a
table. The stored procedure sp_unmakestartup can be run to remove
sp_sqlregister as a startup stored procedure.
11. Forward-Only Cursors Default to Dynamic Cursors
In SQL Server 6.5, forward-only cursors are dynamic by default, which
allows faster cursor opening and also allows the results set to display
updates made to the underlying tables. Dynamic cursors are faster in
version 6.5 and no longer require unique indexes.
Trace flag 7501 disables the dynamic cursor enhancements and reverts to
version 6.0 behavior.
12. Plans for Cursors on Stored Procedures are Cached
SQL Server 6.5 caches plans for cursors for some extended stored
procedures; this provides a gain in performance for many cursor operations.
However it will also use procedure cache to hold these plans, perhaps
affecting an application's caching behavior (and performance). Trace flag
7502 disables this caching of cursor plans.
13. New Service: MSDTC
A new service is installed with SQL Server 6.5. The MSDTC service is the
Distributed Transaction Coordinator which provides cross-server transaction
capabilities (automatic two-phase commit). Normally the MSDTC service
should not affect existing applications (other than the automatic
transactional consistency), however it does require some memory and some
processing time, so it might affect existing applications. The service can
be stopped if its features are not required.
14. USE Statement in EXECUTE Resets on Completion
In SQL Server 6.5, upon completion of EXECUTEing a string that contains a
USE statement, the "current database" will automatically be reset to the
database that was being USEd before the EXECUTE. In SQL Server 6.0, the
"current database" setting persisted after the EXECUTE.
To cause the same behavior as in SQL Server 6.0, each statement that should
be executed in the USEd database must be EXECUTEd as a string with the USE
statement preceding it. The following batch would output "pubs" in SQL
Server 6.0; in SQL Server 6.5 it outputs "master:"
DECLARE @mydb VARCHAR(30)
SELECT @mydb = 'pubs'
EXECUTE('USE ' + @mydb)
To obtain the SQL Server 6.0 behavior in SQL Server 6.5, the following
batch should be used:
DECLARE @mydb VARCHAR(30)
SELECT @mydb = 'pubs'
EXECUTE('USE ' + @mydb + 'SELECT db_name()')
This batch carries out the SELECT in the USEd database but returns to the
master database upon completion.
15. ODBC Driver Settings
Microsoft Knowledge Base Article 149921
discusses some of the ANSI setting
changes in the Microsoft SQL Server 2.65.0201 ODBC driver that might affect
applications. Generally, these are all caused by the following SET options
that force ANSI compliance:
SET TEXTSIZE 2147483647
SET ANSI_DEFAULTS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
16. JOIN Syntax and Trace Flag 204
Although the outer join operators *= and =* from earlier versions of SQL
Server are supported, you cannot use both outer join operators and ANSI-SQL
style joined tables in the same query.
When trace flag 204 is enabled, only SELECT statement syntax from SQL
Server versions 6.0 and earlier is permitted; joined tables, derived
tables, and other ANSI features are not permitted.
17. Objects Created in a Transaction
SQL Server 6.5 allows objects to be created within a transaction. These
operations are protected by the standard transactional rules and may be
committed or rolled back as necessary. However, creating an object in a
transaction causes locks to be held on system tables in the database until
the creating transaction commits or rolls back. Use caution when you create
objects inside a transaction; this includes the creation of temporary
objects in the tempdb database.
18. SELECT-INTO is Now an Atomic Operation
SELECT-INTO is now an atomic operation and holds exclusive locks on
sysindexes, sysobjects, and syscolumns for the duration of the SELECT-INTO,
or the whole transaction if inside a transaction.
19. ISQL/W Connections Reduced Due to Larger Packet Sizes
The number of ISQL/w connections on Win16 clients is affected by the
Network Packet Size configuration value of SQL Server. The smaller the
Network Packet Size (minimum 512 bytes), the more simultaneous connections
you can make from the same DB-Library client, because the larger the
network packet size, the more system resources are used on the Windows 3.x
client. If the Network Packet Size on the server side is configured to be
512, you should be able to make the same number of connections as you could
in SQL Server 6.0 from the same client. For more information, see Microsoft
Knowledge Base article 150909
"INF: Number of Connections for SQL Server
6.5 Win16 Clients."
20. Global Variables Disallowed in CHECK and DEFAULT Constraints
Global variables (such as @@SPID and so forth) cannot be used in CHECK or
DEFAULT constraints (in either CREATE TABLE or ALTER TABLE statements).
This has never been documented as a valid option and is now flagged as
invalid syntax, producing error 112:
Variables are not allowed in CREATE TABLE statement.
Built-in functions continue to work in constraints.
21. Books Online: New Viewer
The SQL Server 6.5 Books Online uses the InfoView.exe program to view
the text rather than the MSIN32.exe that was used previously.
SQL Server does not have to be installed in order to read the Books
Online; they can be installed independently by doing the following:
- Create a directory (c:\sqlbks perhaps)
- Copy the \sqlbks65\sqlbooks.* files and \i386\InfoView.exe from
the CD-ROM into that directory. MSIN32.EXE from 6.0 will not work
as the reader for the 6.5 books, you do need InfoView.exe (it
doesn't need any DLLs).
- Create a Program Manager item with:
A command line of c:\sqlbks\infoview.exe sqlbooks.mvb
A working directory of c:\sqlbks
You should then be able to read the online books.
22. VBSQL.ocx Replaces VBSQL.vbx
A Visual Basic 3.0 project that uses the old 16-bit DB-Library for Visual
Basic, VBSQL.vbx, should be ported to the new DB-Library for Visual Basic
OLE custom control, VBSQL.ocx. For more information, see SQL Server 6.5
Books Online, "Porting an Old DB-Library for Visual Basic Project."
23. Graphical Showplan Has Been Removed
Due to changes in the SHOWPLAN output, the graphical ShowPlan tabs are no
longer available in the SQL Enterprise Manager's Query Tool and in ISQL/w.
24. Extended Stored Procedures
Because of changes to underlying structures, all extended stored procedures
written in the C programming language must be recompiled from the C source
code and relinked under Microsoft SQL Server version 6.5 to OPENDS60.LIB.
Although in SQL Server 6.0 you could call back in to the server from an XP,
this was unsupported. Such "loopback" connections are supported in SQL
Server 6.5 through the use of bound connections in which several
connections can share the same transaction lock space and the same
transaction, and can work on the same data without lock conflicts.
25. Future Concerns
SQL Server 6.5 includes new features that supersede the functionality of
some previous features. Although all features from SQL Server 6.0 continue
to be supported in 6.5, future versions of SQL Server might not support
some statements where the same functionality can be achieved using other
means. For example, the following features are supported in 6.5 but might
be discontinued in future versions:
- Browse Mode: Although SELECT FOR BROWSE is supported in 6.5, the
functionality of the FOR BROWSE clause in SELECT statements can now be
achieved more efficiently by using cursors.
- Device Mirroring within SQL Server: If your installation of SQL Server
is currently using SQL Server mirroring, it is recommended that you
use the mirroring functionality of Windows NT or hardware-based
- Outer Join Syntax: With SQL Server 6.5, the '*=' and '=*' syntax for
outer joins in a WHERE clause can be replaced with the ANSI-standard
join syntax in the FROM clause, using:
LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
- Double Quoted Strings: The use of the double-quote character should be
reserved for delimited or quoted identifiers. Although the double-quote
can still be used to delimit a character string (rather than an object
name), if SET QUOTED_IDENTIFIER ON is executed, any string delimited by
double quotes will be assumed to be an identifier.
- Not Equal (!=): The ANSI standard syntax for representing 'not equal' is
'<>'. This should be used in place of '!=' in all cases.
- Null Comparison (=NULL): The ANSI standard is IS NULL and should be used
in all cases. In fact, if SET ANSI_NULLS ON is in effect, "= NULL"
will return FALSE in all cases, because ANSI specifies that no value
(even NULL itself) is equal to NULL.
- Trace Flags: SQL Trace should be used for monitoring the receive buffer
instead of using trace flags 4030 and 4032.
According to Books Online, trace flags should be used to
temporarily work around a problem until a permanent solution is put in
place. Although the information provided by trace flags can help you
diagnose problems, keep in mind that trace flags are not part of the
supported feature set. This means that future compatibility or
continued use is not assured.
Discontinuing the use of the following features should also be considered:
- Segments: User-defined segments are often used to cause database objects
to be placed on certain devices for performance reasons. The use of
multi-disk RAID devices generally will provide a greater increase in
performance with a lower associated administrative cost.
- The DB-Library Two-Phase Commit Library: The Distributed Transaction
Coordinator now provides this capability automatically.