Distributed Query Architecture

SQL Server Architecture

SQL Server Architecture

Distributed Query Architecture

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

  • Linked server names

    The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin 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 2000, 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 or OPENDATASOURCE functions are 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 2000 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 OLE DB Provider Reference for Distributed Queries.

When 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. SQL Server queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider. For more information, see SQL Dialect Requirements for OLE DB Providers.

SQL Server 2000 specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. This lets the SQL Server query optimizer better analyze the pattern of data in the data source against the requirements of each SQL statement, increasing the ability of the query optimizer to generate optimal execution plans. For more information, see Distribution Statistics Requirements for OLE DB Providers.

See Also

Configuring Linked Servers

Distributed Queries