Distributed Query Architecture

Microsoft® SQL Server™ version 7.0 supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQL statements:

• Linked server names

  The system stored procedures sp_addlinkedsrv and sp_addlinkedserverlogin are used to give a server name to an OLE DB data source. Objects in these linked servers can be referenced in Transact-SQL statements using four-part names. For example, if a linked server name of DeptSQLSrvr is defined against another copy of SQL Server 7.0, the following statement references a table on that server:

  SELECT * FROM DeptSQLSrvr.Northwind.dbo.Employees

    

  The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. This rowset can then be referenced like a table in Transact-SQL statements.

• Ad hoc connector names

  For infrequent references to a data source, the OPENROWSET function is specified with the information needed to connect to the linked server. The rowset can then be referenced the same way a table is referenced in Transact-SQL statements:

  SELECT *

  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

          'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';

          Employees)

    

SQL Server 7.0 uses OLE DB to communicate between the relational engine and the storage engine. The relational engine breaks down each Transact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.

The relational engine uses the OLE DB API to open the rowsets on linked servers, to fetch the rows, and to manage transactions.

For each OLE DB data source accessed as a linked server, an OLE DB provider must be present on the server running SQL Server. The set of Transact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider. For more information, see the OLE DB Provider Reference for Distributed Queries.

Whenever possible, SQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. SQL Server does not default to scanning the base table into SQL Server and performing the relational operations itself.

See Also