What's New in Microsoft SQL Server 2000

Microsoft® SQL Server™ 2000 extends the performance, reliability, quality, and ease-of-use of Microsoft SQL Server version 7.0. Microsoft SQL Server 2000 includes several new features that make it an excellent database platform for large-scale online transactional processing (OLTP), data warehousing, and e-commerce applications.

The OLAP Services feature available in SQL Server version 7.0 is now called SQL Server 2000 Analysis Services. The term OLAP Services has been replaced with the term Analysis Services. Analysis Services also includes a new data mining component. For more information, see What's New in Analysis Services.

The Repository component available in SQL Server version 7.0 is now called Microsoft SQL Server 2000 Meta Data Services. References to the component now use the term Meta Data Services. The term repository is used only in reference to the repository engine within Meta Data Services. For more information, see What's New in Meta Data Services.

The What's New topics contain brief overviews of the new features and links to relevant conceptual topics that provide more detailed information. These conceptual topics provide links to topics that describe the commands or statements you use to work with these features.


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 and AFTER Triggers

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.


XML Integration of Relational Data

The Microsoft® SQL Server™ 2000 relational database engine natively supports Extensible Markup Language (XML).

You can now access SQL Server 2000 over HTTP using a Universal Resource Locator (URL). You can define a virtual root on a Microsoft Internet Information Services (IIS) server, which gives you HTTP access to the data and XML functionality of SQL Server 2000.

You can use HTTP, ADO, or OLE DB to work with the XML functionality of SQL Server 2000:

  • You can define XML views of SQL Server 2000 databases by annotating XML-Data Reduced (XDR) schemas to map the tables, views, and columns that are associated with the elements and attributes of the schema. The XML views can then be referenced in XPath queries, which retrieve results from the database and return them as XML documents.

  • The results of SELECT statements can be returned as XML documents. The SQL Server 2000 Transact-SQL SELECT statement supports a FOR XML clause that specifies that the statement results be returned in the form of an XML document instead of a relational result set. Complex queries, or queries that you want to make secure, can be stored as templates in an IIS virtual root, and executed by referencing the template name.

  • You can expose the data from an XML document as a relational rowset using the new OPENXML rowset function. OPENXML can be used everywhere a rowset function can be used in a Transact-SQL statement, such as in place of a table or view reference in a FROM clause. This allows you to use the data in XML documents to insert, update, or delete data in the tables of the database, including modifying multiple rows in multiple tables in a single operation.

See Also

SQL Server and XML Support

XML and Internet Support Overview


Graphical Administration Enhancements

Microsoft® SQL Server™ 2000 introduces these graphical administration improvements and new features:

Log Shipping

Log shipping allows the transaction logs from a source database to be continually backed up and loaded into a target database on another server. This is useful for maintaining a warm standby server, or for offloading query processing from the source server to a read-only destination server. For more information, see Log Shipping.

SQL Profiler Enhancements

SQL Profiler now supports size-based and time-based traces, and includes new events for Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow, Log File Auto Shrink, Show Plan All, Show Plan Statistics, and Show Plan Text.

SQL Profiler has been enhanced to provide auditing of SQL Server activities, up to the auditing levels required by the C2 level of security defined by the United States government. For more information, see Auditing SQL Server Activity and Monitoring with SQL Profiler.

SQL Query Analyzer Enhancements

SQL Query Analyzer now includes Object Browser, which allows you to navigate through and get information (such as parameters and dependencies) about database objects, including user and system tables, views, stored procedures, extended stored procedures, and functions. The Object Browser also supports generating scripts to either execute or create objects. Other enhancements include server tracing and client statistics that show information about the server-side and client-side impact of a given query.

SQL Query Analyzer includes a stored procedure debugger. SQL Query Analyzer also includes templates that can be used as the starting points for creating objects such as databases, tables, views, and stored procedures. For more information, see SQL Query Analyzer and Overview of SQL Query Analyzer.

Copy Database Wizard

Users can run the Copy Database Wizard to upgrade SQL Server version 7.0 databases to SQL Server 2000 databases. It can also be used to copy complete databases between instances of SQL Server 2000. For more information, see Copying Databases to Other Servers and Upgrading Databases from SQL Server 7.0 (Copy Database Wizard).


Replication Enhancements

Microsoft® SQL Server™ 2000 introduces the following replication improvements and new features:

Implementing Replication

SQL Server 2000 enhances snapshot replication, transactional replication, and merge replication by adding:
  • Alternate snapshot locations, which provide easier and more flexible methods for applying the initial snapshot to Subscribers. You can save (and compress) the snapshot files to a network location or removable media, which can then be transferred to Subscribers without using the network.

  • Attachable subscription databases, which allow you to transfer a database with replicated data and one or more subscriptions from one Subscriber to another SQL Server. After the database is attached to the new Subscriber, the subscription database at the new Subscriber will automatically receive its own pull subscriptions to the publications at the specified Publishers.

  • Schema changes on publication databases, which allow you to add or drop columns on the publishing table and propagate those changes to Subscribers.

  • On demand script execution, which allows you to post a general SQL script that will be executed at all Subscribers.

  • Pre- and post-snapshot scripts, which allow you to run scripts before or after a snapshot is applied at the Subscriber.

  • Remote agent activation, which allows you to reduce the amount of processing on the Distributor or Subscriber by running the Distribution Agent or Merge Agent on one computer while activating that agent from another computer. You can use remote agent activation with push or pull subscriptions.

  • Support of new SQL Server features, which includes user-defined functions, indexed views, new data types, and multiple instances of SQL Server.

  • The ActiveX Snapshot Control, which makes programmatic generation of snapshots easier.

  • More snapshot scripting options, which support transfer of indexes, extended properties, and constraints to Subscribers.

Merge Replication

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the changes between sites when they are connected. Enhancements to merge replication include:
  • Greater parallelism of the Merge Agent for improved server-to-server performance.

  • Optimizations for determining data changes relevant to a partition at a Subscriber.

  • Dynamic snapshots, which provide more efficient application of the initial snapshot when using dynamic filters.

  • Vertical filters for merge publications.

  • More powerful dynamic filtering with user-defined functions.

  • The ability to use alternate synchronization partners when synchronizing data. Using alternate synchronization partners, a Subscriber to a merge publication can synchronize with any specified server that has the same data as the original Publisher.

  • Automated management of identity ranges. In merge replication topologies where a publication contains an identity column, and where new rows can be inserted at Subscribers, automated management of identity ranges at the Subscriber ensures the same identity values are not assigned to rows inserted at different subscription databases, and that primary key constraint violations do not occur. This feature is also available when queued updating is used with snapshot replication or transactional replication.

  • Support for timestamp columns in published tables.

  • Improved management of the growth of merge tracking data.

  • Several new merge replication conflict resolvers including interactive resolvers that provide a user interface for immediate, manual conflict resolution, priority based on a column value, minimum/maximum value wins, first/last change wins, additive/average value, and merge by appending different text values.

  • New options to validate permissions for a Subscriber to upload changes to a Publisher (check_permissions) and security enhancements including code signing of conflict resolvers included with Microsoft SQL Server 2000.

  • New COM interfaces that support heterogeneous data sources as Publishers within a SQL Server replication topology.

  • Validation of replicated data per subscription or on a publication-wide basis. Validation is also available through SQL Server Enterprise Manager.

  • Reinitialization to allow uploading of changes from the Subscriber before the application of a new snapshot.

For more information, see Merge Replication and Replication Options.

Transactional Replication

With transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. Enhancements to transactional replication include:
  • Concurrent snapshot processing so that data modifications can continue on publishing tables while the initial snapshot is generated.

  • Improved error handling and the ability to skip specified errors and continue replication.

  • Validation of replicated data at the Subscriber, including validation on vertical partitions. Validation is also available through SQL Server Enterprise Manager.

  • Publishing indexed views as tables.

  • The option to store data modifications made at the Subscriber in a queue (queued updating).

  • The option to transform data as it is published to Subscribers (transforming published data).

  • The ability to restore transactional replication databases without reinitializing subscriptions or disabling and reconfiguring publishing and distribution. You can also set up transactional replication to work with log shipping, enabling you to fail over to a warm standby server without reconfiguring replication. For more information, see Strategies for Backing Up and Restoring Transactional Replication.

For more information, see Transactional Replication.

Queued Updating

Queued updating allows snapshot replication and transactional replication Subscribers to modify published data without requiring an active network connection to the Publisher.

When you create a publication with the queued updating option enabled and a Subscriber performs INSERT, UPDATE, or DELETE statements on published data, the changes are stored in a queue. The queued transactions are applied asynchronously at the Publisher when network connectivity is restored.

Because the updates are propagated asynchronously to the Publisher, the same data may have been updated by the Publisher or by another Subscriber and conflicts can occur when applying the updates. Conflicts are detected automatically and several options for resolving conflicts are offered.

For more information, see Queued Updating.

Transforming Published Data

Transformable subscriptions (available with snapshot replication or transactional replication) leverages the data movement, transformation mapping, and filtering capabilities of Data Transformation Services (DTS).

Using transformable subscriptions in your replication topology allows you to customize and send published data based on the requirements of individual Subscribers, including performing data type mappings, column manipulations, string manipulations, and use of functions as data is published.

For more information, see Transforming Published Data.

Replication Usability

There have been several improvements in SQL Server Enterprise Manager that provide for easier implementation, monitoring, and administration of replication. Enhancements to replication usability include:
  • A centralized Replication folder in the SQL Server Enterprise Manager tree, which organizes all subscriptions and publications on the server being administered.

  • The ability to browse for and subscribe to publications (when permission is allowed) using Windows Active Directory.

  • The ability to see multiple Distributors in a single monitoring node in SQL Server Enterprise Manager.

  • Standard and advanced replication options separated in the Create Publication, Create Push Subscription, and Create Pull Subscription Wizards. You can choose to show advanced options in these wizards on the Welcome page of each wizard.

  • New wizards for creating jobs that create dynamic snapshots for merge publications that use dynamic filters (Create Dynamic Snapshot Job Wizard), and for transforming published data in snapshot replication or transactional replication (Transform Published Data Wizard).


Data Transformation Services Enhancements

Microsoft® SQL Server™ 2000 introduces these Data Transformation Services (DTS) enhancements and new features:

New Custom Tasks

New DTS custom tasks, available through DTS Designer or the DTS object model, allow you to create DTS packages that perform tasks or set variables based on the properties of the run-time environment. Use these tasks to:
  • Import data from, and send data and completed packages to, Internet and File Transfer Protocol (FTP) sites.

  • Run packages asynchronously.

  • Build packages that send messages to each other.

  • Build packages that execute other packages.

  • Join multiple package executions as part of a transaction.

For more information, see Building a DTS Custom Task.

Enhanced Logging Facilities

DTS package logs save information for each package execution, allowing you to maintain a complete execution history. You can also view execution information for individual processes within a task.

You can generate exception files for transformation tasks. When you log to exception files, you can save source and destination error rows to a file through the DTS OLE DB text file provider and re-process the error rows.

Saving DTS Packages to Visual Basic Files

DTS packages now can be saved to a Microsoft® Visual Basic® file. This allows a package created by the DTS Import/Export Wizard or DTS Designer to be incorporated into Visual Basic programs or to be used as prototypes by Visual Basic developers who need to reference the components of the DTS object model. For more information, see Saving a DTS Package.

Using the Multiphase Data Pump

A new multiphase data pump allows advanced users to customize the operation of the data pump at various stages of its operation. You can now use global variables as input and output parameters for queries. For more information, see Multiphase Data Pump Functionality..

Using Parameterized Queries

You can now use parameterized source queries in a DTS transformation task and an Execute SQL task. In addition, DTS includes an option for saving the results of a parameterized query to a global variable, allowing you to perform functions such as saving disconnected Microsoft ActiveX® Data Objects (ADO) recordsets in DTS. For more information, see Using Parameterized Queries in DTS.

Using Global Variables to Pass Information Between DTS Packages

You now can use the Execute Package task to dynamically assign the values of global variables from a parent package to a child package. Use global variables to pass information from one package to another when each package performs different work items. For example, use one package to download data on a nightly basis, summarize the data, assign summary data values to global variables, and pass the values to another package that further processes the data.


Analysis Services Enhancements

Microsoft® SQL Server™ 2000 Analysis Services includes the former OLAP Services. It also includes a new data mining component. For more information about data mining and the other new features of Analysis Services, see What's New in Analysis Services.


Meta Data Services Enhancements

Microsoft® SQL Server™ 2000 Meta Data Services includes the former Microsoft Repository. For more information about the new features of Meta Data Services, see What's New in Meta Data Services.


English Query Enhancements

English Query introduces new features such as:

  • Greater integration with Microsoft® Visual Studio®, Analysis Services, and Full-Text Search.

  • A graphical user interface for English Query authoring.

  • The SQL Project Wizard.

  • An XML-based language for persisting English Query model information.

For more information, see What's New in English Query.


Documentation Enhancements

These enhancements and new features have been made to the documentation for Microsoft® SQL Server™ 2000:

F1 Help Integrated With SQL Server Books Online

The F1 Help for SQL Server 2000 has been integrated with SQL Server Books Online. When you select F1 Help, a related topic in SQL Server Books Online is displayed.

SQL Server Books Online Supports Subsets

SQL Server Books Online allows you to define subsets of the entire SQL Server Books Online against which to perform a search. You can define your own subsets; however, a set of default subsets is also provided. The subsets are targeted at specific audiences, and each subset covers multiple topics. Subsets make it easier to search for information related to your activities by minimizing the number of search hits.

Thumbnail Art

Some diagrams in SQL Server Books Online now appear as thumbnail diagrams. The full diagram can be viewed by clicking Enlarge diagram. The full diagram appears when the topic is printed.

Glossary Improvements

Glossary terms appear as colored, underlined text. When clicked, a pop-up window appears with the term definition.

Easier to Access Related Topics

Many topics have a list of related topics, called See Also, at the end. SQL Server Books Online adds a See Also icon that displays the list of related topics when you click it.