General Server Enhancements

SQL Server 6.0 has significantly enhanced the features listed in the following table. The enhancements are then described in detail.

CASE Expression

The ANSI-compliant CASE expression allows SQL expressions to be simplified for conditional values (such as to classify certain values in a results set). The CASE expression provides extremely powerful capabilities in SQL Server 6.0. The CASE expression is ANSI SQL-92-compliant and allowed anywhere an expression is used. For details, see the CASE Expression topic.

Comments

In SQL Server 6.0, ANSI-compliant comments can be defined using the double hyphen (--) identifier. ANSI-style comments should be used for all comments that are nested. The earlier comment style (/* text_of_comments */) is still available for multiline comments, but it should not be used for nested comments. For details, see the Comments topic.

Configuration

SQL Server 6.0 includes many new standard and advanced configuration options. Some configuration options that used to be set up by using the SQL Setup utility are now available as advanced options.

For details of the new options, whether standard or advanced, see the sp_configure system stored procedure.

Database Consistency Checker

DBCC, the database consistency checker, includes many new options and enhancements to previously existing options. For details, see the DBCC statement.

Datatypes

Datatypes specify the data characteristics of columns, stored-procedure parameters, and local variables.

SQL Server 6.0 includes these datatype changes:

• Exact precision datatypes, decimal and numeric, have been added. For details, see the Datatypes topic.

  ·For ANSI compatibility, configurable session- or database-level default column nullability is used to determine the nullability of a column or a user-defined datatype when the nullability is not explicitly defined. The ANSI NULL default (default allows null values) is configurable for the client session (with the SET statement) or for the entire database (with the sp_dboption system stored procedure). Additionally, a system function (GETANSINULL) has been added that allows the current nullability setting to be queried. For information about precedence and rules of nullability, see the CREATE TABLE statement. For information about session- or database-level default nullability, see the SET statement and the sp_dboption system stored procedure. For details on system functions, see the Functions topic.

• Datatype synonyms including the VARYING keyword can be used anywhere a datatype is specified. Support has been added for binary varying, character, character varying, char varying, double precision, and integer. For details, see the Datatypes topic.
• The special system datatype timestamp has been changed to binary(8). For details, see the Datatypes topic.
• For ANSI compliance, datatypes are case-insensitive regardless of the case-sensitivity of the server. Datatype names can be entered in any case. For details, see the Datatypes topic.
• Text support has been improved. You can now send a text parameter to a stored procedure, and you can update text with the new UPDATETEXT statement. For details, see the CREATE PROCEDURE statement, and the Datatypes and Text and Image Manipulation topics.

Double Quote Delimited Identifiers

Double quote delimited identifiers allow the server to skip evaluation of the text that appears within double quotation marks ("). The text within double quotation marks can include any kind of character in the installed code page except for double quotation marks. This feature provides ANSI compatibility for delimited identifiers. And although not recommended, keywords specified within double quotation marks can now be used as object names. For details, see the Identifiers topic and the SET statement.

Dynamically-built String Execution

The EXECUTE statement has been enhanced to support the execution of a character string constructed at execution time as a Transact-SQL batch. This allows for object names and Transact-SQL syntax to be resolved at execution time rather than compile time. For details, see the EXECUTE statement.

Functions

Built-in functions allow access to database or server information from within an expression such as a WHERE clause or the SELECT statement. Many of these functions have been updated for use with new features. The mathematical functions have been updated to allow numeric and decimal datatypes.

These system functions have been added:

• COALESCE
• GETANSINULL
• IDENT_INCR
• IDENT_SEED
• NULLIF
• STATS_DATE

For details, see the Functions topic.

Keywords

Numerous keywords and reserved words have been added in SQL Server 6.0. Keywords include words that cannot be used for object or column names. Reserved words include words that, for future compatibility reasons, should be avoided for object or column names. For details and a complete list of keywords and reserved words, see the Keywords topic.

Error Handling

The RAISERROR statement has been enhanced to allow argument substitution to an error message (similar to the C printf capabilities). The severity of RAISERROR messages can now be configured. When creating a RAISERROR message, a user-defined message can now be created or a message can be referenced from the sysmessages system table. To create user-defined messages, three new system procedures have been added. For details, see the RAISERROR statement.

Server Cursors

SQL Server 6.0 supports forward, backward, absolute, and relative-position server cursors. The advantages of server cursors, which follow and extend standard ANSI conventions, can be seen explicitly through Transact-SQL cursors and transparently through built-in server functions accessed by client programs using DB-Library™ or ODBC. In addition to the statements used for cursor declaration and manipulation (DECLARE, OPEN, FETCH, and so on), the DELETE and UPDATE statements have been modified for use within a cursor with the WHERE CURRENT OF clause.

For information about creating, removing, and using cursors by using Transact-SQL statements, see the Cursors topic. For information about manipulating cursor data (a single "fetched" row), see the DELETE and UPDATE statements.

Session Settings

The SET command now includes several options that control default datatype nullability, whether double quotation mark-delimited identifiers are evaluated by the server, how identity values are handled, and the transaction isolation level. For details, see the SET statement.

Stored Procedures

System stored procedures provide a consistent method for updating the system catalog (the tables in the master database used to maintain and track overall system configuration). Some stored procedures have been added, and many existing system procedures have been enhanced.

These are the categories of system-supplied stored procedures:

Catalog Stored Procedures

Catalog stored procedures provide a consistent mechanism for access to database information in SQL Server as well as database gateways. Several catalog stored procedures have been added. For details, see the Stored Procedures¾Catalog Stored Procedures topic.

Extended Stored Procedures

Extended stored procedures provide a way to dynamically load and execute a function with a dynamic-link library (DLL) in a manner similar to a stored procedure. Many new extended stored procedures have been added. For details on using general features, such as setting up and administering mail, see the Microsoft SQL Server Administrator's Companion. For details about the individual procedures and the information returned, see the Stored Procedures¾Extended Stored Procedures topic.

Replication Stored Procedures

Replication stored procedures have been added for use by SQL Enterprise Manager; however, replication can be set up manually through these new stored procedures. For details on setting up subscriber and publisher databases to be used in replication, see the Microsoft SQL Server Administrator's Companion. For details and syntax about the procedures used for replication, see the Stored Procedures¾Replication Stored Procedures topic.

SQL Executive Stored Procedures

Alert management and scheduling stored procedures have been added for use by SQL Enterprise Manager. Scheduling events and alerts can also be set up manually. For details on managing alerts and scheduling events, see the Microsoft SQL Server Administrator's Companion. For details about the procedures used with SQL Executive, see the Stored Procedures¾SQL Executive Stored Procedures topic.

System Stored Procedures

In addition to adding functionality to existing system stored procedures, some system stored procedures have been included for backward compatibility only. These stored procedures should not be expected to be upgraded or maintained in future releases of SQL Server. For details, see the Stored Procedures¾System Stored Procedures topic.

Additionally, some undocumented procedures may exist. These procedures are for internal use or have been replaced by new features. For example, some stored procedures used only to record primary key and foreign key relationships still exist but are no longer included in Microsoft SQL Server documentation because constraints should now be used to enforce referential integrity. Use these procedures only if you have used them with earlier releases or as examples for your own procedures. The undocumented procedures are for internal use and backward compatibility only; they will not be maintained and may not be included in future releases.

System Tables

System tables store information about the database (database catalog¾tables found in all databases) or about the system (system catalog¾tables found only in the master database). Because new features such as replication and constraints require information to be maintained in one or all of these databases, certain tables (replication tables and SQL Executive tables) have been added. For details on replication, the distribution database, and managing alerts with the SQL Executive, see the Microsoft SQL Server Administrator's Companion. For details on these tables, see the System Tables ¾ Database Catalog topic.

Utilities and Executables

The bldmastr command-line utility has been replaced by the SQL Setup graphical user interface. All options previously available only through bldmastr have been moved or have been enhanced in SQL Setup.

The isql command-line utility has been enhanced for integrated login security and includes enhanced error handling on exit.

For details, see the Utilities and Executables topic.

Variables

Global variables are maintained by the system and give specific information about the server or the current user's activity. Because of new features in SQL Server 6.0, many new global variables have been added. For details, see the Variables topic.