Microsoft® SQL Server™ version 7.0 simplifies the development, deployment, maintenance, and management of your database applications with its redesigned architecture. SQL Server 7.0 supports applications that span a broad range of platforms: from personal systems, such as desktop and notebook computers, to high-end symmetric multiprocessing (SMP) servers with 8 to 16 processors, several gigabytes of memory, and a terabyte or more of disk storage.
The major architectural advancements include:
These new formats allow the server to easily scale from low-end to high-end systems, improving performance and manageability. They also enable new capabilities in future releases, such as embedded tables, multiple types of rows on a single data page, and databases that can be electronically mailed.
SQL Server 7.0 includes new backup and restore mechanisms and DBCC algorithms. The bulk copy capabilities are more tightly integrated with the query processor and support the new Unicode and GUID data types.
SQL Server 7.0 supports complete row-level locking on both data pages and index pages.
SQL Server 7.0 includes revisions to SQL Server’s query-processing capabilities. The improved query processor supports new query execution algorithms such as index intersection and hash joins as well as parallel query execution, designed to speed up the processing of queries over very large databases. The query optimizer uses an improved costing model and new techniques for plan selection. The query process also supports distributed and heterogeneous query capabilities for accessing data from any OLE DB and ODBC data source.
Additional enhancements include the following:
SQL Server 7.0 simplifies the relationship between a SQL Server database and the Microsoft Windows® file system, enabling greater scalability.
In SQL Server 7.0, databases reside on operating-system files instead of on SQL Server logical devices. You can create a database and all its files with a single CREATE DATABASE statement. SQL Server Enterprise Manager can create new databases and modify (alter) existing databases.
Database devices and segments no longer reside on top of the operating-system files; rather, a SQL Server 7.0 database consists of two or more Windows files. Each Windows file is used by only one database. A single file cannot be shared by multiple databases. When a database is dropped, its files are also deleted.
SQL Server 7.0 allows database files to expand automatically, eliminating the need for administrators to issue an additional ALTER statement. SQL Server 7.0 includes new CREATE DATABASE syntax that incorporates this file-based implementation.
Filegroups are supported. Filegroups are similar to the user-defined segments available in earlier versions of SQL Server.
SQL Server 7.0 supports movable databases, the ability to move a database by moving, copying, or e-mailing the database files from one server to another and attaching them to the copy of SQL Server running on the target server.
All database pages are now 8 KB in size, increased from 2 KB. The maximum number of bytes in a row is now 8060 bytes, and the limit on character and binary data types is 8000 bytes, increased from 255 bytes. Tables can now have 1024 columns, a significant increase over the 250 columns supported earlier. The new page and row formats support row-level locking, are extensible for future requirements, and improve performance when large blocks of data are accessed, because each I/O operation retrieves more data.
In earlier versions of SQL Server, nonclustered indexes used physical record identifiers (page number, row number) as row locators. For example, if the leaf node of a clustered index (data page) was split, many rows were moved to a new data page, and thus had new physical record identifiers. All of the nonclustered indexes had to be updated with these new physical record identifiers, which could require a significant amount of time and resources.
SQL Server 7.0 uses an improved index design. If a table has a clustered index (and thus a clustering key), the leaf nodes of all nonclustered indexes use the clustering key as the row locator rather than the physical record identifier. If a table does not have a clustered index, nonclustered indexes continue to use the physical record identifiers to point to the data pages. In both cases, the row locator is stable. When a leaf node of a clustered index is split, SQL Server 7.0 does not need to update the nonclustered indexes because the row locators are still valid. If a table does not have a clustered index, page splits do not occur.
Earlier versions of SQL Server employed no more than one index per table in a query. SQL Server 7.0 uses index intersection and index union to take advantage of multiple indexes in a query. SQL Server 7.0 also uses unique row identifiers to join two indexes on the same table.
Storage of text and image data has been redesigned. SQL Server 7.0 can store more than one text or image value on a single data page. Space for small text or image values is thus used more efficiently. SQL Server 7.0 uses parallel retrieval of text and image data to optimize retrieval of large objects.
The locking subsystem has changed significantly. Most importantly, SQL Server 7.0 now supports full row-level locking for both data rows and index entries. The SQL Server 7.0 lock manager has been optimized to complete lock requests faster and with less internal synchronization. Many OLTP applications can experience increased concurrency, especially when applications append rows to tables and indexes.
The SQL Server 7.0 lock manager adjusts the resources it uses for larger databases dynamically, eliminating the need to adjust the locks server configuration option manually. SQL Server 7.0 chooses automatically between page locking (preferable for table scans) and row-level locking (preferable for inserting, updating, and deleting data).
Before SQL Server 7.0, the log was a system table (syslogs) that used ordinary database pages. These log pages were allocated and deallocated just like pages of other tables, and they competed with data pages for space in the memory cache.
The SQL Server 7.0 transaction log is significantly different. The SQL Server 7.0 log consists of one or more log files, each containing a contiguous set of log records. The log is no longer represented as a system table. The SQL Server 7.0 log design enables larger I/Os to the log than were possible earlier.
The tempdb system database expands automatically as needed. For example, a reporting application may be submitted that needs a lot of space in tempdb. Rather than failing with an out-of-space error, tempdb grows automatically to the size needed to support the report. SQL Server 7.0 reinitializes tempdb to the configured size the next time the server is started.
The SQL Server 7.0 query processor has been redesigned to support the large databases and complex queries found in decision support, data warehouse, and OLAP applications. The query processor includes several new execution strategies that can improve the performance of complex queries. It now uses hash join, merge join, and hash aggregation techniques. These techniques can scale to larger databases than those supported by the nested loops join technique only. SQL Server 7.0 uses index intersection and union techniques on multiple indexes to filter data before it retrieves rows from the database. All indexes on a table are maintained concurrently and constraint evaluations are part of the query processor’s execution plan. These two factors simplify and speed the updating of multiple rows of a table.
The SQL Server 7.0 query processor automatically generates statistics about index key distributions using efficient sampling algorithms. Distribution statistics reflect how likely it is that an index key value will uniquely identify rows in the table and is used by the query optimizer when it is determining how to access tables. Having the server automatically refresh these statistics ensures that the query optimizer can build efficient query execution plans and eliminates the need to manually update the statistics.
The SQL Server 7.0 query optimizer has a wide set of execution strategies, and many of the optimization limitations of earlier versions of SQL Server have been removed. An improved costing model and compile-time enhancements, such as predicate transitivity and constant folding, greatly improve the quality of query plans. The cost-based optimizer can be rapidly adapted for new or refined execution strategies.
To examine how the SQL Server 7.0 query processor executes a query, view the Plan tab of SQL Server Query Analyzer or the Transact-SQL SHOWPLAN statement output. The query processor supports new query hints, and some query limitations have been loosened or removed. For example, a single query can now reference 256 tables, and the number of internal work tables used by a query (16 in earlier versions) is no longer limited.
The query processor uses OLE DB to communicate with the data-storage components of SQL Server. OLE DB provides the SQL Server 7.0 query processor with distributed and heterogeneous query capabilities. It supports distributed queries between multiple SQL Server 7.0 servers as well as to any OLE DB provider. For more information about distributed queries, see Distributed Queries or Distributed Query Architecture.
| ALTER DATABASE | SET SHOWPLAN_ALL |
| CREATE DATABASE | SET SHOWPLAN_TEXT |
| CREATE INDEX | sp_lock |
| Indexes | syslockinfo |
| Locking | Analyzing a Query |