One of the goals for SQL Server 7.0 is to provide improved query processor support for a range of database activities, including large queries, complex queries, data warehousing, and online analytical processing (OLAP). These are some of the specific ways in which this goal is addressed by SQL Server:
Earlier versions of SQL Server provided limited ways of optimizing queries. For example, SQL Server 6.5 supports only one method for performing a join: nested loops iteration. SQL Server 7.0 adds hash join and merge join, which give the optimizer more options to choose from and are the algorithms of choice for many large queries.
SQL Server 7.0 improves execution of a plan after it is chosen. Faster scans, sort improvements, and large memory support all offer potential performance advantages.
Symmetric multiprocessing (SMP) computers and striped disk sets are increasingly common. SQL Server 6.5 implemented parallel I/O and inter-query parallelism (assigning different queries to different processors), but it could not execute different parts of a single query in parallel. SQL Server 7.0 breaks a single query into multiple subtasks and distributes them across multiple processors for parallel execution.
Star schemas and star queries are common in data warehousing applications. A star query joins a large central table, called a fact table, to one or more smaller tables, called dimension tables. SQL Server 7.0 recognizes these queries automatically and makes a cost-based choice among multiple types of star join plans.