Distributed queries access data from multiple heterogeneous data sources, which can be stored in either the same or different computers. Microsoft® SQL Server™ supports distributed queries by using OLE DB, the Microsoft specification of an application programming interface (API) for universal data access.
Distributed queries provide SQL Server users with access to:
OLE DB providers expose their data in tabular objects called rowsets. SQL Server version 7.0 allows rowsets from OLE DB providers to be referenced in Transact-SQL statements as if they were a SQL Server table.
In earlier versions of SQL Server, accessing data external to SQL Server could be accomplished by using either Open Data Services gateways or remote stored procedures (on other SQL Server computers). The distributed queries introduced with SQL Server 7.0 are more powerful and flexible than the earlier methods because 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 not only traditional relational DBMS systems with SQL query processors, but also data managed by data sources of varying capabilities and sophistication. As long as the software owning the data exposes it in a tabular rowset through an OLE DB provider, this data can be used in distributed queries.
Note Using distributed queries in SQL Server is similar to the linked table functionality through ODBC previously supported by Microsoft Access. This capability is now built into SQL Server with OLE DB as the interface to external data.
Here is a typical scenario for using distributed queries. For example, you are a regional sales manager for a large insurance company and have subsidiaries in several countries. Each regional office selects the product that stores its sales data. The United Kingdom subsidiary stores its data in Oracle; the Australian subsidiary stores its data in Access; the Spanish subsidiary stores data in Microsoft Excel; and the United States subsidiary stores its data in SQL Server. You want a report that lists, on a quarterly basis for the last three years, the insurance policies, the subsidiaries, and the sales representatives with the highest quarterly sales figures. Each of these three queries can be accomplished by using a single distributed query, running on SQL Server.