Database Server

The database server of Microsoft® SQL Server™ has two main parts; the relational engine (RE) and the storage engine (SE). One of the most important architectural changes made in SQL Server version 7.0 is to clearly 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 briefly summarized as:

1. The relational engine compiles the SELECT statement into an optimized execution plan. The execution plan defines a series of operations against simple 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.

2. The relational engine then uses the OLE DB API to request that the storage engine open the rowsets.
3. 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.
4. The relational engine combines the data from the storage engine rowsets into the final result set transmitted back to the user.