Distributed Queries

Distributed queries access data from multiple heterogeneous data sources. These data sources can be stored on either the same or different computers. Microsoft SQL Server supports distributed queries by using OLE DB.

SQL Server users can use distributed queries to access the following:

  • Distributed data stored in multiple instances of SQL Server.

  • Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.

OLE DB providers expose data in tabular objects called rowsets. SQL Server allows rowsets from OLE DB providers to be referenced in Transact-SQL statements as if they were SQL Server tables.

Tables and views in external data sources can be referenced directly in SELECT, INSERT, UPDATE, and DELETE Transact-SQL statements. Because distributed queries use OLE DB as the underlying interface, distributed queries can access traditional relational DBMS systems that have SQL query processors, and can also access data managed by data sources of varying capabilities and sophistication. As long as the software that owns the data exposes the data in a tabular rowset through an OLE DB provider, the data can be used in distributed queries.

Note

Using distributed queries in SQL Server is similar to the linked table functionality through ODBC. This functionality, which was supported previously by Microsoft Access, is now built into SQL Server using OLE DB as the interface to external data.

The following illustration shows the connections between a client computer, an instance of SQL Server, and an OLE DB provider.

Client to SQL Server to OLE DB provider

Important Topics About Distributed Queries

The following table lists topics that provide information to help you understand and use distributed queries to access data that is either on separate instances of SQL Server or on data sources other than SQL Server.

For information about

See

Configuring OLE DB providers and linked servers

Configuring Linked Servers

Configuring OLE DB Providers for Distributed Queries

Distributed queries in distributed transactions

Distributed Queries and Distributed Transactions

Specific OLE DB providers

OLE DB Providers Tested with SQL Server

Language support for OLE DB providers

SQL Dialect Requirements for OLE DB Providers

Linked servers

Accessing External Data

ODBC and distributed queries

Schema Functions Supporting Distributed Queries

Performance issues

Optimizing Distributed Queries

Developer reference for the OLE DB provider

OLE DB Provider Reference for Distributed Queries

Statistics requirements

Distribution Statistics Requirements for OLE DB Providers

Rules and restrictions for distributed queries and linked servers, and for allowable Transact-SQL statements.

Guidelines for Distributed Queries

System Stored Procedures