Transact-SQL Highlights

SQL Server 6.5 adds new Transact-SQL capabilities and provides some significant enhancements. SQL Server 6.5 features comply with ANSI SQL-92 standards and with the Federal Information Processing Standards (FIPS 127-2) as established by NIST. The new CUBE and ROLLUP operators are useful in writing data warehousing and decision support system applications. Transact-SQL changes for SQL Server 6.5 extend the ability to temporarily disable constraints during replication, thereby eliminating the effort required to drop and then add constraints as with earlier versions of SQL Server; provide the ability to dump and load a single table; provide the ability to use Data Definition Language (DDL) statements within transactions; and enable row-level locking during INSERT operations. Other Transact-SQL enhancements include:

Bound Connections

Bound connections are enabled for SQL Server 6.5. With bound connections, two or more discrete connections can share the same transaction lock space and the same transaction, allowing them to work on the same data without lock conflicts. This allows extended stored procedures to call back into the database within the lock context of the parent transaction.

INSERT...EXECUTE

The INSERT INTO statement now supports the EXECute stored_procedure clause in place of insert variables or constants, simplifying the way that the return values from stored procedures are handled. The results set from executing the stored procedure is inserted into a table or variable as specified in the INSERT statement.

CUBE and ROLLUP Operators

The CUBE and ROLLUP operators of the SELECT statement deliver aggregates and super-aggregates for elements within a GROUP BY statement.

The CUBE operator delivers a multi-dimensional data results set instead of the typical zero- or one-dimensional data results set and is useful for cross-referencing information without having to write additional procedures. The CUBE operator creates all combinations of groupings from the list of columns in the GROUP BY statement. You can use the CUBE operator to produce results sets that you can use to enhance charts, graphs, and summary tables.

The ROLLUP operator produces cumulative aggregates, such as running sums or running averages, that can be incorporated into reports, charts, and graphs. The ROLLUP operator differs from the CUBE operator in that it creates groupings by moving in only one direction, from right to left, along the list of columns in the GROUP BY statement. It then applies the aggregate function to all but the rightmost column of these groupings. The ROLLUP operator is useful for extracting statistics and summary information from results sets.

Insert Row-level Locking (IRL)

The lock manager has been enhanced to provide row-level locking (IRL) for most INSERT operations. IRL improves performance in situations where access contention and hotspots (areas of unusually high access) occur.