INF: Changes to SQL Server 6.5 That Affect 6.0 Apps (152032)

MORE INFORMATION

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 Transact-SQL."

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.

4. Keywords

The following words, reserved in SQL Server 6.0, are keywords in SQL Server 6.5:

   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 these changes.

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 example:

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

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:"

   GO
   USE master
   GO
   DECLARE @mydb VARCHAR(30)
   SELECT @mydb = 'pubs'
   EXECUTE('USE ' + @mydb)
   go
   SELECT db_name()
   go

To obtain the SQL Server 6.0 behavior in SQL Server 6.5, the following batch should be used:

   GO
   USE master
   GO
   DECLARE @mydb VARCHAR(30)
   SELECT @mydb = 'pubs'
   EXECUTE('USE ' + @mydb + 'SELECT db_name()')
   go

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 mirroring instead.
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.