- New SQL Server 2000 Features
- SQL Server 2000 Enhancements
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
SQL Server 2000 Enhancements
In addition to the entirely new features provided by SQL Server 2000 are a number of enhancements to existing features.
Index Enhancements
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 11, "Creating and Managing Indexes in SQL Server."
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.
Collation Enhancements
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 Enhancements
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).
Full-Text Search Enhancements
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 45, "Full Text Search."
Clustering Enhancements
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 44, "SQL Server Clustering."
Backup and Restore Enhancements
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 18, "Database Backup and Recovery," and Chapter 28, "Transaction Management and the Transaction Log."
Up to 64GB Memory Support
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 Data Center server.
Analysis Services Enhancements
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."