Distributed Queries

In addition to storing and searching data locally, Microsoft SQL Server 7.0 also can be used as a gateway to many other data stores, both relational and nonrelational data sources.

SQL Server 7.0 performs distributed queries, that is, queries that involve data from two or more servers. It supports retrievals, updates, and cursors across servers, and ensures transaction semantics across nodes using the Microsoft Distributed Transaction Coordinator (MS DTC). It also maintains security across servers.

If any remote servers support indexes or SQL queries, the SQL Server query optimizer determines the largest possible query that can be sent to each remote server. In other words, the query optimizer assigns the maximum possible data reduction to each remote server. For example, if a remote query is issued against a 1-million row table, with a WHERE clause or an aggregation that returns only 10 records, the 1 million rows are processed at the remote server, and only 10 records are sent across the network. This reduces network traffic and overall query time. Typical operations that are pushed toward the data source are selections, joins, and sorts.