Database Engine Components
The relational database server of Microsoft® SQL Server™ 2000 has two main parts: the relational engine and the storage engine. One of the most important architectural changes made in SQL Server version 7.0 was to strictly separate the relational and storage engine components within the server and to have them use the OLE DB API to communicate with each other.
The processing for a SELECT statement that references only tables in local databases can be summarized as:
- The relational engine compiles the SELECT statement into an optimized execution plan. The execution plan defines a series of operations against basic rowsets from the individual tables or indexes referenced in the SELECT statement.
A rowset is the OLE DB term for a result set. The rowsets requested by the relational engine return the amount of data needed from a table or index to perform one of the operations used to build the SELECT result set. For example, this SELECT statement requires a table scan if it references a table with no indexes:
SELECT * FROM ScanTable
The relational engine implements the table scan by requesting one rowset containing all the rows from ScanTable.
This SELECT statement only needs information available in an index:
SELECT DISTINCT LastName FROM Northwind.dbo.Employees
The relational engine implements the index scan by requesting one rowset containing the leaf rows from the index built on the LastName column.
This SELECT statement needs information from two indexes:
SELECT CompanyName, OrderID, ShippedDate FROM Northwind.dbo.Customers AS Cst JOIN Northwind.dbo.Orders AS Ord ON (Cst.CustomerID = Ord.CustomerID)
The relational engine requests two rowsets, one for the clustered index on Customers and the other on one of the nonclustered indexes in Orders.
- The relational engine uses the OLE DB API to request that the storage engine open the rowsets.
- As the relational engine works through the steps of the execution plan and needs data, it uses OLE DB to fetch the individual rows from the rowsets it requested the storage engine to open. The storage engine transfers the data from the data buffers to the relational engine.
- The relational engine combines the data from the storage engine rowsets into the final result set transmitted back to the user.