MSSQLServer2000 linked server

[Ranthalion]

Hi,

I hope this is the appropriate location for this question. I am using SQL Server 2000 on two different servers and have created linked server connections on both of them, so they can communicate with each other.

When I create a query that accesses a remote table on the linked server, how is the where statement evaluated?

I have read contradictory information on the msdn site (although, it's probably just my interpretation that is contradictory). I read that the query will be passed to the linked server and it will be evaluated by the data provider up to its capabilities. On the other hand, I've also read that SQL server will scan the entire remote table and perform the query evaluations locally...

So, when I have a query that accesses data on a linked server where does the 'where' clause get evaluated? Does the provider pass the entire table back to the local server, or only the rows that satisfy the where clause? Where would sorting and grouping take place, on the local server or the provider?

Thanks,

Ranthalion

[hspc]

From MSDN (http://msdn2.microsoft.com/en-us/library/aa178113%28SQL.80%29.aspx):
SQL Server attempts to delegate as much of the evaluation of a distributed query to the SQL Command Provider as possible
So it is according to the type of the query.
Can you post links and quotes from the conflicting information?

[Ranthalion]

About 3/4 down under Pure Table Scans <a href="http://msdn2.microsoft.com/en-us/library/aa902681(sql.80).aspx">MSDN</a>:

SQL Server scans the entire remote table from the provider and performs all query evaluation locally.


The quote you referenced, combined with this one, seem contradictory to me. I agreed with what you are saying, up until I read this line. Since I'm linking 2 sql 2000 servers, the whole query should be evaluated on the provider, but the sentence I referenced seems to say otherwise.

Thanks,
Ranthalion

[hspc]

This pargraph explains it:


Query Execution Scenarios When evaluating a distributed query, SQL Server interacts with the OLE DB provider in one or more of these scenarios:


Remote query
Indexed access
Pure table scans
UPDATE and DELETE statements
INSERT statement
Pass-through queries

scanning the entire table occurs in case of Pure table scans scenario.