SQL Server 2000's Coolest Features

Just when you think you have a handle on SQL Server 7.0, Microsoft ships a new SQL Server release. SQL Server 2000 might skip 1,993 release numbers, but you won't find the same degree of difference between SQL Server 2000 and 7.0 as you found between SQL Server 7.0 and 6.5. Even so, this latest release is full of new functionality. Here are seven of my favorite new SQL Server 2000 features.

7. Integrated XML Support

XML support is key to Microsoft's push to Web-enable SQL Server 2000. The new release supports T-SQL extensions that let SQL Server produce a result set as an XML document. In addition, you can use XML documents to directly insert and update SQL Server tables.

6. INSTEAD OF Triggers

SQL Server 2000's new INSTEAD OF triggers aren't exactly like BEFORE triggers, but you can use them in a similar way. Unlike AFTER triggers, which the system executes after a transaction changes the database, INSTEAD OF triggers execute instead of the triggering action.

5. Table Data Type

The new table data type lets an application store temporary results as a table that you can manipulate by using a select statement or even action queries—just as you can manipulate any standard user table.

4. User-Defined Functions

User-defined functions enhance T-SQL's programmability by letting you create and use your own functions in T-SQL stored procedures and batches. SQL Server 2000 executes user-defined functions in the same way that it executes T-SQL's built-in functions, and user-defined functions can return either scalar variables or table data types.

3. Indexed Views

Unlike standard views, in which SQL Server resolves the data-access path dynamically at execution time, the new indexed views feature lets you store views in the database just as you store tables. Indexed views, which are persistent, can significantly improve application performance by eliminating the work that the query processor must perform to resolve the views.

2. Federated Database Support

Federated database support, which lets you use SQL Server 2000's distributed partitioned views to horizontally partition tables across multiple servers, enabled SQL Server 2000 to set its new Transaction Processing Performance Council (TPC) TPC-C benchmark of 262,243 transactions per minute (tpmC) with 12 clustered Compaq systems.

1. Cascading DRI

Cascading Declarative Referential Integrity (DRI) might not be SQL Server's sexiest new feature, but it's the feature you'll most likely use. With cascading DRI, you specify a relationship between a parent and a dependent table so that a deletion or update of a row in the parent table automatically cascades to corresponding rows in the dependent table.