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 2005 supports distributed queries by using OLE DB. For information about new and enhanced functionality for distributed queries, see Database Engine Distributed Queries Enhancements.

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.

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

See Also

Concepts

Collations in Distributed Queries
Handling Errors in Server-to-Server Remote Stored Procedures
Distributed Queries and Distributed Transactions
Configuring Linked Servers for Delegation

Help and Information

Getting SQL Server 2005 Assistance