New SQL Server 2000 Features

What does SQL Server 2000 have to offer over SQL Server 7? The following is a list of the new features provided in SQL Server 2000:

  • User-defined functions

  • Indexed views

  • Distributed partitioned views

  • INSTEAD OF and AFTER triggers

  • New datatypes

  • Cascading RI constraints

  • Multiple SQL Server instances

  • XML support

  • Log shipping

The rest of this section takes a closer look at each of these new features and provides a reference to subsequent chapters where more information about the new feature can be found.

User-Defined Functions

SQL Server has always provided the ability to store and execute SQL code routines via stored procedures. In addition, SQL Server has always supplied a number of built-in functions. Functions can be used almost anywhere an expression can be specified in a query. This was one of the shortcomings of stored procedures?they couldn't be used inline in queries in select lists, where clauses, and so on. Perhaps you want to write a routine to calculate the last business day of the month. With a stored procedure, you have to execute the procedure, passing in the current month as a parameter and returning the value into an output variable, and then use the variable in your queries. If only you could write your own function that you could use directly in the query just like a system function. In SQL Server 2000, you can.

SQL Server 2000 introduces the long-awaited support for user-defined functions. User-defined functions can take zero or more input parameters and return a single value?either a scalar value like the system-defined functions, or a table result. Table-valued functions can be used anywhere table or view expressions can be used in queries, and they can perform more complex logic than is allowed in a view.

For more information on defining, managing, and using user-defined functions, see Chapter 30, "User-Defined Functions."

Indexed Views

Views are often used to simplify complex queries, and they can contain joins and aggregate functions. However, in the past, queries against views were resolved to queries against the underlying base tables, and any aggregates were recalculated each time you ran a query against the view. In SQL Server 2000 Enterprise or Developer Edition, you can define indexes on views to improve query performance against the view. When creating an index on a view, the result set of the view is stored and indexed in the database. Existing applications can take advantage of the performance improvements without needing to be modified.

Indexed views can improve performance for the following types of queries:

  • Joins and aggregations that process many rows

  • Join and aggregation operations that are performed frequently within many queries

  • Decision support queries that rely on summarized, aggregated data that is infrequently updated

For more information on designing, using, and maintaining indexed views, see Chapter 27, "Creating and Managing Views in SQL Server."

Distributed Partitioned Views

SQL Server 7.0 provided the ability to create partitioned views using the UNION ALL statement in a view definition. It was limited, however, in that all the tables had to reside within the same SQL Server where the view was defined. SQL Server 2000 expands the ability to create partitioned views by allowing you to horizontally partition tables across multiple SQL Servers. The feature helps you scale out one database server to multiple database servers, while making the data appear as if it comes from a single table on a single SQL Server. In addition, partitioned views are now able to be updated.

For more information on defining and using distributed partitioned views, see Chapter 27 and Chapter 21, "Administering Very Large SQL Server Databases."


In versions of SQL Server prior to 7.0, a table could not have more than one trigger defined for INSERT, UPDATE, and DELETE. These triggers only fired after the data modification took place. SQL Server 7.0 introduced the ability to define multiple AFTER triggers for the same operation on a table. SQL Server 2000 extends this capability by providing the ability to define which AFTER trigger fires first and which fires last. (Any other AFTER triggers besides the first and last will fire in an undetermined order.)

SQL Server 2000 also introduces the ability to define INSTEAD OF triggers. INSTEAD OF triggers can be specified on both tables and views. (AFTER triggers can still only be specified on tables.) If an INSTEAD OF trigger is defined on a table or view, the trigger will be executed in place of the data modification action for which it is defined. The data modification is not executed unless the SQL code to perform it is included in the trigger definition.

For more information on creating, managing, and using INSTEAD OF and AFTER triggers, see Chapter 29, "Creating and Managing Triggers."

New Datatypes

SQL Server 2000 introduces three new datatypes. Two of these can be used as datatypes for local variables, stored procedure parameters and return values, user-defined function parameters and return values, or table columns:

  • bigint? An 8-byte integer that can store values from ?263 (?9223372036854775808) through 263-1 (9223372036854775807).

  • sql_variant? A variable-sized column that can store values of various SQL Server?supported data types, with the exception of text, ntext, timestamp, and sql_variant.

The third new datatype, the table datatype, can be used only as a local variable datatype within functions, stored procedures, and SQL batches. The table datatype cannot be passed as a parameter to functions or stored procedures, nor can it be used as a column datatype. A variable defined with the table datatype can be used to store a result set for later processing. A table variable can be used in queries anywhere a table can be specified.

For more information on using the new datatypes, see Chapter 26, "Using Transact-SQL in SQL Server 2000."

Text in Row Data

In previous versions of SQL Server, text and image data was always stored on a separate page chain from where the actual data row resided. The data row contained only a pointer to the text or image page chain, regardless of the size of the text or image data. SQL Server 2000 provides a new text in row table option that allows small text and image data values to be placed directly in the data row, instead of requiring a separate data page. This can reduce the amount of space required to store small text and image data values, as well as reduce the amount of I/O required to retrieve rows containing small text and image data values.

For more information on specifying text and image datatypes for tables, see Chapter 12, "Creating and Managing Tables in SQL Server." For more information on how text and image data is stored in tables, see Chapter 33, "SQL Server Internals."

Cascading RI Constraints

In previous versions of SQL Server, referential integrity (RI) constraints were restrictive only. If an insert, update, or delete operation violated referential integrity, it was aborted with an error message. SQL Server 2000 provides the ability to specify the action to take when a column referenced by a foreign key constraint is updated or deleted. You can still abort the update or delete if related foreign key records exist by specifying the NO ACTION option, or you can specify the new CASCADE option, which will cascade the update or delete operation to the related foreign key records.

See Chapter 14, "Implementing Data Integrity," for more information on using the new options with foreign key constraints.

Multiple SQL Server Instances

Previous versions of SQL Server supported the running of only a single instance of SQL Server at a time on a computer. Running multiple instances or multiple versions of SQL Server required switching back and forth between the different instances, requiring changes in the Windows registry. (The SQL Server Switch provided with 7.0 to switch between 7.0 and 6.5 performed the registry changes for you.)

SQL Server 2000 provides support for running multiple instances of SQL Server on the same system. This allows you to simultaneously run one instance of SQL Server 6.5 or 7.0 along with one or more instances of SQL Server 2000. Each SQL Server instance runs independently of the others and has its own set of system and user databases, security configuration, and so on. Applications can connect to the different instances in the same way they connect to different SQL Servers on different machines.

This feature provides the ability to run an older version of SQL Server alongside SQL Server 2000, as well as the ability to run separate environments (for example, a development and test environment) on the same computer.

For more information on setting up one or more SQL Server instances, see Chapter 8, "Installing and Upgrading SQL Server."

XML Support

Extensible Markup Language has become a standard in Web-related programming to describe the contents of a set of data and how the data should be output or displayed on a Web page. XML, like HTML, is derived from the Standard Generalized Markup Language (SGML). When linking a Web application to SQL Server, a translation needs to take place from the result set returned from SQL Server to a format that can be understood and displayed by a Web application. Previously, this translation needed to be done in a client application.

SQL Server 2000 provides native support for XML. This new feature provides the ability to do the following:

  • Return query result sets directly in XML format.

  • Retrieve data from an XML document as if it were a SQL Server table.

  • Access SQL Server through a URL using HTTP. Through Internet Information Services (IIS), you can define a virtual root that gives you HTTP access to the data and XML functionality of SQL Server 2000.

The latest version of SQLXML, version 3.0, extends the built-in XML capabilities of SQL Server 2000 with technology to create XML Web services from SQL Server stored procedures or server-side XML templates. SQLXML 3.0 also includes extensions to the .NET Framework that provide SQLXML programmability to the languages supported by Microsoft Visual Studio .NET, including C# and Microsoft Visual Basic .NET.

To help you make the most of the XML capabilities of SQL Server 2000, Microsoft provides, as a free download from its Web site, the Microsoft SQL Server 2000 Web Services Toolkit, which consists of tools, code, samples, and whitepapers for building XML Web services and Web applications with SQL Server 2000. You can find the link to download the installer file in the download area of the MSDN Web site ( It is currently located in the .NET Enterprise Server/SQL Server Development area.

For more information on using XML with SQL Server, see Chapter 41, "Using XML in SQL Server 2000."

Log Shipping

The Enterprise Edition of SQL Server 2000 now supports log shipping, which you can use to copy and load transaction log backups from one database to one or more databases on a constant basis. This allows you to have a primary read/write database with one or more read-only copies of the database that are kept synchronized by restoring the logs from the primary database. The destination database can be used as a warm standby for the primary database, for which you can switch users over in the event of a primary database failure. Additionally, log shipping provides a way to offload read-only query processing from the primary database to the destination database.

This capability was available in previous versions of SQL Server, but it required the DBA to manually set up the process and schedule the jobs to copy and restore the log backups. SQL Server 2000 officially supports log shipping and has made it easier to set up via the Database Maintenance Plan Wizard. This greatly simplifies the process by automatically generating the jobs and configuring the databases to support log shipping.

For more information on configuring and using log shipping, see Chapter 22, "Data Replication."

Notification Services

A new component is now available for SQL Server 2000 that makes it easy to build applications that forward messages to end users. This feature is called SQL Server Notification Services. SQL Server Notification Services is a platform for the development and deployment of notification applications. Notification applications send messages to users based upon subscriptions that they set up in advance. Depending on how the subscriptions are configured, messages can be sent to the subscriber immediately or on a predetermined schedule. The messages sent can be personalized to reflect the preferences of the subscriber.

The Notification Services platform provides a reliable, high-performance server that is built on the .NET Framework and SQL Server 2000 and runs on the Microsoft Windows Server family of operating systems. Notification Services was designed for scalability and efficiency: It can support applications with millions of subscribers and large volumes of data. As a platform, it is extensible and provides interoperability with a variety of existing applications.

SQL Server serves as the matching engine for notification applications, as well as the storage location for the subscription information. The subscriber and delivery information is stored in a central Notification Services database, and individual subscription information is stored in application-specific databases.

For more information on the Notification Server architecture and configuring and using SQL Server Notification Services, see Chapter 45, "SQL Server Notification Services."

Microsoft SQL Server 2000 Driver for JDBC

Microsoft recently released its JDBC driver for SQL Server 2000 as a free download for all SQL Server 2000 customers. The Microsoft SQL Server 2000 Driver for JDBC is a Type 4 JDBC driver that provides highly scalable and reliable connectivity for the enterprise Java environment. The current release of the SQL Server 2000 Driver for JDBC supports the JDBC 2.0 specification.

The SQL Server 2000 Driver for JDBC provides JDBC access to SQL Server 2000 from any Java-enabled applet, application, or application server. The JDBC driver provides enterprise features like support for XA transactions, server-side cursors, SQL_Variant datatypes, updateable resultsets, and more.

The SQL Server 2000 Driver for JDBC supports the Java Developer's Kit versions 1.1.8, 1.2, and 1.3 and is supported on the following operating systems:

  • Microsoft Windows® XP

  • Microsoft Windows 2000 with Service Pack 2 or higher

  • AIX

  • HP-UX

  • Solaris

  • Linux

For more information on the Microsoft SQL Server 2000 Driver for JDBC, see Chapter 9, "Client Installation and Configuration."

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features