Relational Database Enhancements

SQL2000 Whats New

What's New

Relational Database Enhancements

Microsoft® SQL Server™ 2000 introduces several server improvements and new features:

XML Support

The relational database engine can return data as Extensible Markup Language (XML) documents. Additionally, XML can also be used to insert, update, and delete values in the database. For more information, see SQL Server and XML Support and XML and Internet Support Overview.

Federated Database Servers

SQL Server 2000 supports enhancements to distributed partitioned views that allow you to partition tables horizontally across multiple servers. This allows you to scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers. This group, or federation, of database servers can support the data storage requirements of the largest Web sites and enterprise data processing systems. For more information, see Federated SQL Server 2000 Servers.

SQL Server 2000 introduces Net-Library support for Virtual Interface Architecture (VIA) system-area networks that provide high-speed connectivity between servers, such as between application servers and database servers. For more information, see Communication Components.

User-Defined Functions

The programmability of Transact-SQL can be extended by creating your own Transact-SQL functions. A user-defined function can return either a scalar value or a table. For more information, see SQL User-Defined Functions.

Indexed Views

Indexed views can significantly improve the performance of an application where queries frequently perform certain joins or aggregations. An indexed view allows indexes to be created on views, where the result set of the view is stored and indexed in the database. Existing applications do not need to be modified to take advantage of the performance improvements with indexed views. For more information, see SQL Views.

New Data Types

SQL Server 2000 introduces three new data types. bigint is an 8-byte integer type. sql_variant is a type that allows the storage of data values of different data types. table is a type that allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user-defined functions. For more information, see Data Types and Table Structures.


INSTEAD OF triggers are executed instead of the triggering action (for example, INSERT, UPDATE, DELETE). They can also be defined on views, in which case they greatly extend the types of updates a view can support. AFTER triggers fire after the triggering action. SQL Server 2000 introduces the ability to specify which AFTER triggers fire first and last. For more information, see Triggers.

Cascading Referential Integrity Constraints

You can control the actions SQL Server 2000 takes when you attempt to update or delete a key to which existing foreign keys point. This is controlled by the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. For more information, see Constraints.

Collation Enhancements

SQL Server 2000 replaces code pages and sort orders with collations. SQL Server 2000 includes support for most collations supported in earlier versions of SQL Server, and introduces a new set of collations based on Windows collations. You can now specify collations at the database level or at the column level. Previously, code pages and sort orders could be specified only at the server level and applied to all databases on a server. For more information, see Collations.

Collations support code page translations. Operations with char and varchar operands having different code pages are now supported. Code page translations are not supported for text operands. You can use ALTER DATABASE to change the default collation of a database. For more information, see SQL Server Collation Fundamentals and ALTER DATABASE.

Full-Text Search Enhancements

Full-text search now includes change tracking and image filtering. Change tracking maintains a log of all changes to the full-text indexed data. You can update the full-text index with these changes by flushing the log manually, on a schedule, or as they occur, using the background update index option. Image filtering allows you to index and query documents stored in image columns. The user provides the document type in a column that contains the file name extension that the document would have had if it were stored as a file in the file system. Using this information, full-text search is able to load the appropriate document filter to extract textual information for indexing. For more information, see Microsoft Search Service.

Multiple Instances of SQL Server

SQL Server 2000 supports running multiple instances of the relational database engine on the same computer. Each computer can run one instance of the relational database engine from SQL Server version 6.5 or 7.0, along with one or more instances of the database engine from SQL Server 2000. Each instance has its own set of system and user databases. Applications can connect to each instance on a computer similar to the way they connect to instances of SQL Servers running on different computers. The SQL Server 2000 utilities and administration tools have been enhanced to work with multiple instances. For more information, see Multiple Instances of SQL Server.

Index Enhancements

You can now create indexes on computed columns. You can specify whether indexes are built in ascending or descending order, and if the database engine should use parallel scanning and sorting during index creation. For more information, see Table Indexes and Parallel Operations Creating Indexes.

The CREATE INDEX statement can now use the tempdb database as a work area for the sorts required to build an index. This results in improved disk read and write patterns for the index creation step, and makes it more likely that index pages will be allocated in contiguous strips. In addition, the complete process of creating an index is eligible for parallel operations, not only the initial table scan. For more information, see tempdb and Index Creation, Parallel Operations Creating Indexes, and CREATE INDEX.

Failover Clustering Enhancements

The administration of failover clusters has been greatly improved to make it very easy to install, configure, and maintain a Microsoft SQL Server 2000 failover cluster. Additional enhancements include the ability to failover and failback to or from any node in a SQL Server 2000 cluster, the ability to add or remove a node from the cluster through SQL Server 2000 Setup, and the ability to reinstall or rebuild a cluster instance on any node in the cluster without affecting the other cluster node instances. The SQL Server 2000 utilities and administration tools have been enhanced to work with failover clusters. For more information, see Failover Clustering Architecture.

Net-Library Enhancements

The SQL Server 2000 Net-Libraries have been rewritten to virtually eliminate the need to administer Net-Library configurations on client computers when connecting SQL Server 2000 clients to instances of SQL Server 2000. The new Net-Libraries also support connections to multiple instances of SQL Server on the same computer, and support Secure Sockets Layer encryption over all Net-Libraries. SQL Server 2000 introduces Net-Library support for Virtual Interface Architecture (VIA) system-area networks that provide high-speed connectivity between servers, such as between application servers and database servers. For more information, see Communication Components.

64-GB Memory Support

Microsoft SQL Server 2000 Enterprise Edition can use the Microsoft Windows 2000 Advanced Windows Extension (AWE) API to support up to 64 GB of physical memory (RAM) on a computer. For more information, see Using AWE Memory on Windows 2000.

Distributed Query Enhancements

SQL Server 2000 introduces a new OPENDATASOURCE function, which you can use to specify ad hoc connection information in a distributed query. SQL Server 2000 also specifies methods that OLE DB providers can use to report the level of SQL syntax supported by the provider and statistics on the distribution of key values in the data source. The distributed query optimizer can then use this information to reduce the amount of data that has to be sent from the OLE DB data source. SQL Server 2000 delegates more SQL operations to OLE DB data sources than earlier versions of SQL Server. Distributed queries also support the other functions introduced in SQL Server 2000, such as multiple instances, mixing columns with different collations in result sets, and the new bigint and sql_variant data types. For more information, see Distributed Query Architecture.

SQL Server 2000 distributed queries add support for the OLE DB Provider for Exchange and the Microsoft OLE DB Provider for Microsoft Directory Services. For more information, see OLE DB Provider for Microsoft Directory Services and OLE DB Provider for Exchange.

Updatable Distributed Partitioned Views

SQL Server 2000 introduces enhancements to distributed partitioned views. You can partition tables horizontally across several servers, and define a distributed partitioned view on each member server that makes it appear as if a full copy of the original table is stored on each server. Groups of servers running SQL Server that cooperate in this type of partitioning are called federations of servers. A database federation built using SQL Server 2000 databases is capable of supporting the processing requirements of the largest Web sites or enterprise-level databases. For more information, see Creating a Partitioned View.

Kerberos and Security Delegation

SQL Server 2000 uses Kerberos to support mutual authentication between the client and the server, as well as the ability to pass the security credentials of a client between computers, so that work on a remote server can proceed using the credentials of the impersonated client. With Microsoft Windows® 2000, SQL Server 2000 uses Kerberos and delegation to support both integrated authentication as well as SQL Server logins. For more information, see Security Account Delegation.

Backup and Restore Enhancements

SQL Server 2000 introduces a new, more easily understood model for specifying backup and restore options. The new model makes it clearer that you are balancing increased or decreased exposure to losing work against the performance and log space requirements of different plans. SQL Server 2000 introduces support for recovery to specific points of work using named log marks in the transaction log, and the ability to do partial database restores. For more information, see Backup/Restore Architecture.

Users can define passwords for backup sets and media sets that prevent unauthorized users from accessing SQL Server backups. For more information, see BACKUP.

Scalability Enhancements for Utility Operations

SQL Server 2000 enhancements for utility operations include faster differential backups, parallel Database Console Command (DBCC) checking, and parallel scanning. Differential backups can now be completed in a time that is proportional to the amount of data changed since the last full backup. DBCC can be run without taking shared table locks while scanning tables, thereby enabling them to be run concurrently with update activity on tables. Additionally, DBCC now takes advantage of multiple processors, thus enabling near-linear gain in performance in relation to the number of CPUs (provided that I/O is not a bottleneck). For more information, see Data Integrity Validation and Differential Backup and Restore.

Text in Row Data

SQL Server 2000 supports a new text in row table option that specifies that small text, ntext, and image values be placed directly in the data row instead of in a separate page. This reduces the amount of space used to store small text, ntext, and image data values, and reduces the amount of disk I/O needed to process these values. For more information, see text, ntext, and image Data.