In addition to the entirely new features provided by SQL Server 2000 are a number of enhancements to existing features.
SQL Server 2000 provides enhancements for defining indexes as well as enhancements to the way indexes are built.
Indexes can now be defined on computed columns, and you can specify when creating an index whether it should be built in ascending or descending index key order. SQL Server 2000 also provides an option to use tempdb for performing the sorting operations when creating an index. This can be specified with the WITH SORT_IN_TEMPDB option. When WITH SORT_IN_TEMPDB is specified, SQL Server performs the intermediate sorting required to build this index in tempdb, rather than in the current database. If tempdb is on a separate disk from the destination file group in the current database, building the index will take less time. For more information on the new index creation options, see Chapter 13, "Creating and Managing Indexes."
In addition to the new creation options, SQL Server now supports the use of multiple processors to perform parallel scanning and sorting when creating an index to help speed up index creation.
In previous versions of SQL Server, all databases within a SQL Server had to use the same code page and sort order that SQL Server was configured to use. (This is typically established during SQL Server installation and is not always easy to change.) If you had to restore a database from a server using a different sort order or collation, a normal backup and restore wouldn't work and you would have to bring it over using the Database Migration utility.
SQL Server 2000 now uses collations instead of code pages and sort orders and supports multiple collations within a single SQL Server. You now can specify collations at the database level or at the column level within a table. SQL Server 2000 still supports most collations that were supported in earlier versions of SQL Server, as well as provides support for collations based on Windows collations.
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).
SQL Server 2000 provides enhancements to the Full Text Search capabilities by including change tracking, which maintains a log of all changes to full-text indexed data. SQL Server 2000 also includes image filtering, which allows you to index and query documents stored in image columns.
With change tracking, you can update the full-text index by flushing the change log manually, on a schedule, or as changes occur, using the background update index option.
Image filtering allows you to specify the filename extension that a document would have had if it were stored as a file in the file system. The Full Text Search services can then load the appropriate document filter and extract textual information for indexing from the image data.
For more information on using the Full Text Search services, see Chapter 44, "SQL Server Full-Text Search Services."
In SQL Server 2000, Microsoft has made it much easier to install, configure, and maintain a Microsoft SQL Server 2000 failover cluster. In addition, SQL Server 2000 now provides the ability to failover and failback to or from any node in a SQL Server 2000 cluster, add or remove a node from the cluster using the SQL Server 2000 Setup utility, and reinstall or rebuild a cluster instance on any node without affecting the other cluster node instances. For more information on SQL Server clustering and failover support, see Chapter 24, "SQL Server Clustering."
In SQL Server 2000, passwords can be defined for backup sets and media sets to prevent unauthorized users from restoring sensitive SQL Server backups. SQL Server 2000 also has improved the speed of differential database backups such that they now should complete in a time proportional to the amount of data changed since the last full backup.
SQL Server 2000 also introduces a new model for specifying backup and restore options for your database. Previous database options such as "truncate log on checkpoint" and "select into/bulk copy" have been replaced by three recovery models: Full Recovery, Bulk Logged Recovery, and Simple Recovery. These new models help clarify when you are balancing increased or decreased exposure to losing work against the performance and log space requirements of the different plans.
SQL Server 2000 also provides support for recovering transaction logs to specific points of work using named log marks in the transaction log, as well as the ability to perform partial database restores.
For more information on backing up and restoring databases and database recovery options in SQL Server 2000, see Chapter 16, "Database Backup and Restore," and Chapter 31, "Transaction Management and the Transaction Log."
The Enterprise Editions of SQL Server 2000 can use the Microsoft Windows 2000 Advanced Windows Extension (AWE) API to support up to 8GB of memory on a Windows 2000 Advanced Data Server and 64GB of memory on a Windows 2000 Datacenter server.
What was formerly known as SQL Server OLAP Services is now called SQL Server Analysis Services. Analysis Services provides new and improved features that enhance the capabilities of the previous OLAP Services provided in SQL Server 7.0. A major new feature is the Data Mining component, which can be used to discover information in OLAP cubes and relational databases. In addition, enhancements have been made to the Cube, Dimension, and Hierarchy types to improve and extend the scalability, functionality, and analysis capabilities of cubes. Security enhancements include the ability to assign permissions on cube cells and dimension members to roles. For more information on using Analysis Services and its features and capabilities, see Chapter 42, "Microsoft SQL Server Analysis Services."
At the time of this writing, Microsoft was in the process of beginning beta testing for a 64-bit version of SQL Server 2000, code-named "Liberty." Built to take advantage of hardware enhancements of the 64-bit Itanium platform from Intel, Liberty offers higher levels of single-system scalability for memory-intensive data applications. Currently, the planned hardware offerings support up to 64GB of physical linear memory for the 64-bit version of SQL Server 2000, with up to 4 terabytes of physical linear memory planned for future hardware.
The 64-bit version of SQL Server 2000 includes a 64-bit database server, a 64-bit server agent, and a 64-bit analysis server for OLAP and data mining. The 64-bit version of SQL Server 2000 will require 64-bit hardware running the 64-bit version of Microsoft Windows .NET Server Beta3 or the current release of Windows Advanced Server, Limited Edition running on Intel Itanium processors.
The 64-bit Windows Advanced Server, Limited Edition platform provides the ability to install up to 16 instances of SQL Server 2000 on a single machine, and supports larger numbers of users and applications. This should result in a lower cost of ownership as businesses will require fewer servers to support the same number of users.
The 64-bit components of SQL Server 2000 are code compatible with the 32-bit versions of SQL Server 2000, providing compatibility for organizations that need to maintain some of their 32-bit SQL Server deployments, while introducing new 64-bit technologies for larger, more demanding database applications. All existing 32-bit client applications, including database management and administration tools such as Enterprise Manager, Query Analyzer, and so on, can be used to remotely manage 64-bit SQL Server 2000 installations. In addition, the database storage structures are identical between the 32- and 64-bit versions of SQL Server, so databases can be exchanged between the 32- and 64-bit environments.