While SQL Server 2000 introduces numerous features specifically for Web and knowledge management applications, it also includes features that benefit application programming in general. In particular, it adds features that make it easier to write stored procedures (code that runs on the database server), including the following:

Cascading deletes and updates. SQL Server 2000 can automatically make adjustments to the database after updates or deletes to enforce referential integrity constraints. For example, if an application deletes a purchase order entry in a table, SQL Server can "cascade" that delete to a separate table that holds the purchase order's line items, automatically deleting entries for items in the order. Cascading deletes and updates are standard features of many database servers, but new to SQL Server. SQL Server 7.0 can enforce referential integrity constraints, but only by aborting transactions that try to make changes that violate the constraints; it can't automatically cascade updates or deletes to related tables.

Enhanced triggers. SQL Server 2000 makes several enhancements to triggers, stored procedures that execute automatically when a transaction inserts, updates, or deletes data. It introduces a new INSTEAD-OF trigger type; as the name implies, an INSTEAD-OF trigger executes instead of any change a transaction is trying to make. This feature is useful for validation. For example, an INSTEAD-OF trigger could examine a set of rows a transaction was trying to insert, and then insert only those rows that contained valid data. SQL Server 2000 also lets developers specify the order of execution of conventional SQL Server triggers (now called AFTER triggers), which makes it easier to ensure that multiple triggers which process the same change will have predictable results.

Transact-SQL functions. SQL Server 2000 now allows developers to define functions—stored procedures that return results. Functions have long been a feature of conventional programming languages, where they make it easier to organize complex logic. Transact-SQL functions can return scalar values (e.g., strings, integers), but can also return tables—a convenient way to build complex queries.

New data types. SQL Server 2000 supports a number of new data types, including a 64-bit integer type and a Variant type (familiar to Visual Basic programmers). It introduces a data type for database tables, allowing stored procedures to store tables in variables and return tables from functions. The table data type will be useful in complex stored procedures that generate and store intermediate results in temporary database tables.

Enhanced Query Analyzer. Query Analyzer is a client-side tool that enables developers to interactively send commands to SQL Server. It's the developer's main tool for testing and debugging server-side code. SQL Server 2000's Query Analyzer now includes an object browser pane that enables programmers to graphically review a database's tables, stored procedures, constraints, and other objects. Query Analyzer also includes a debugger for stored procedures, enabling programmers to set breakpoints and trace execution.

Back to associated article: XML Hooks SQL Server into Next-Generation Applications