1. System Table Changes
There have been some changes to system tables: new columns have beenadded, the meaning or contents of some columns have been changed, and somenew system tables have been added. For more information, see SQL Server 6.5Books Online, What's New for SQL Server 6.5, Part 4: "What's New forTransact-SQL."
2. System Stored Procedure Changes and Changes in Format
There have been several changes to system stored procedures. In many casesthe functionality has been expanded to include new SQL Server version 6.5features. In some cases, the format of the output has been modified toprovide 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 thanSQL60 (as in 6.0) or SQL (as in 4.2x). When you upgrade a 4.2x or 6.0installation, the existing directory name is preserved.
The following words, reserved in SQL Server 6.0, are keywords in SQL Server6.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 newkeywords by running ChkUpg65.exe.
5. FROM Clause and Table Names
In SQL Server 6.5, errors are reported when redundant table names appear inthe FROM clause. For example, the SELECT statements given below weresupported in earlier releases but generate errors in SQL Server 6.5. In thefirst SELECT statement, the tables were treated as two different tables. Inthe 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 whethertwo table names identified the same table. For example, pubs.dbo.authorsand pubs..authors were considered to be different tables. Now if two tablenames are not identical, the database IDs and table IDs are compared todetermine whether or not they are the same table. Previously in an Updatestatement SQL Server would simply find the first table in the FROM clausethat matched an unqualified column name and assume that was the table theuser meant. Now this type of query will cause an error, because it isuncertain which table should be updated. Trace flag 110 will disable all ofthese changes.
6. SELECT DISTINCT with ORDER BY
Previous versions of SQL Server allowed SELECT DISTINCT queries containingsort columns in the ORDER BY clause that were not in the select list. Forexample:
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 donot own, you must have REFERENCES permission on the table; this complieswith the ANSI standard. In SQL Server 6.0, only SELECT permission wasrequired 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 acolumn created by a SELECT INTO or CREATE VIEW statement. For example, CREATE VIEW testview AS SELECT au_id, upper(au_lname) FROM authorsresults 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 betweenone and ten inclusive (messages with severity levels ten and under are noterrors, but they do provide additional information). If you set the msg_idby using the WITH SETERROR option, the RAISERROR statement assigns themsg_id to @@ERROR regardless of severity.
In SQL Server version 6.0, @@ERROR is set to 50,000 for messages withseverity levels ten and under.
To revert to SQL Server version 6.0 behavior, use either the SETERRORoption or trace flag 2701.
10. Startup Procedure: sp_sqlregister
In SQL Server 6.5 the stored procedure sp_sqlregister is installed as adefault startup procedure. At startup, sp_sqlregister gathers basicconfiguration information from the operating system, network, and SQLServer, and then broadcasts the SQL Server's presence on the network. Anyserver carrying out xp_sqlinventory can collect the information into atable. The stored procedure sp_unmakestartup can be run to removesp_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, whichallows faster cursor opening and also allows the results set to displayupdates made to the underlying tables. Dynamic cursors are faster inversion 6.5 and no longer require unique indexes.
Trace flag 7501 disables the dynamic cursor enhancements and reverts toversion 6.0 behavior.
12. Plans for Cursors on Stored Procedures are Cached
SQL Server 6.5 caches plans for cursors for some extended storedprocedures; this provides a gain in performance for many cursor operations.However it will also use procedure cache to hold these plans, perhapsaffecting an application's caching behavior (and performance). Trace flag7502 disables this caching of cursor plans.
13. New Service: MSDTC
A new service is installed with SQL Server 6.5. The MSDTC service is theDistributed Transaction Coordinator which provides cross-server transactioncapabilities (automatic two-phase commit). Normally the MSDTC serviceshould not affect existing applications (other than the automatictransactional consistency), however it does require some memory and someprocessing time, so it might affect existing applications. The service canbe 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 aUSE statement, the "current database" will automatically be reset to thedatabase 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 shouldbe executed in the USEd database must be EXECUTEd as a string with the USEstatement preceding it. The following batch would output "pubs" in SQLServer 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 followingbatch 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 themaster database upon completion.
15. ODBC Driver Settings
Microsoft Knowledge Base Article 149921
discusses some of the ANSI settingchanges in the Microsoft SQL Server 2.65.0201 ODBC driver that might affectapplications. Generally, these are all caused by the following SET optionsthat 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 SQLServer are supported, you cannot use both outer join operators and ANSI-SQLstyle joined tables in the same query.
When trace flag 204 is enabled, only SELECT statement syntax from SQLServer versions 6.0 and earlier is permitted; joined tables, derivedtables, 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. Theseoperations are protected by the standard transactional rules and may becommitted or rolled back as necessary. However, creating an object in atransaction causes locks to be held on system tables in the database untilthe creating transaction commits or rolls back. Use caution when you createobjects inside a transaction; this includes the creation of temporaryobjects in the tempdb database.
18. SELECT-INTO is Now an Atomic Operation
SELECT-INTO is now an atomic operation and holds exclusive locks onsysindexes, 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 theNetwork Packet Size configuration value of SQL Server. The smaller theNetwork Packet Size (minimum 512 bytes), the more simultaneous connectionsyou can make from the same DB-Library client, because the larger thenetwork packet size, the more system resources are used on the Windows 3.xclient. If the Network Packet Size on the server side is configured to be512, you should be able to make the same number of connections as you couldin SQL Server 6.0 from the same client. For more information, see MicrosoftKnowledge Base article 150909
"INF: Number of Connections for SQL Server6.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 orDEFAULT constraints (in either CREATE TABLE or ALTER TABLE statements).This has never been documented as a valid option and is now flagged asinvalid 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 viewthe text rather than the MSIN32.exe that was used previously.
SQL Server does not have to be installed in order to read the BooksOnline; 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 VisualBasic, VBSQL.vbx, should be ported to the new DB-Library for Visual BasicOLE custom control, VBSQL.ocx. For more information, see SQL Server 6.5Books 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 nolonger 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 procedureswritten in the C programming language must be recompiled from the C sourcecode 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 SQLServer 6.5 through the use of bound connections in which severalconnections can share the same transaction lock space and the sametransaction, and can work on the same data without lock conflicts.
25. Future Concerns
SQL Server 6.5 includes new features that supersede the functionality ofsome previous features. Although all features from SQL Server 6.0 continueto be supported in 6.5, future versions of SQL Server might not supportsome statements where the same functionality can be achieved using othermeans. For example, the following features are supported in 6.5 but mightbe 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.