Search code examples
sqlsql-serversql-server-2005openquery

Does the query from the linked server still continue even though you have obtained your result in your SQL Server?

Let's say you have a link server X.

If you were to get 1 row from X in a table (apples) from a schema (fruit). Assuming that the table has a billion rows. 

Select TOP 1 * from openquery(X , 'SELECT * FROM fruit.apples')

Does the query still continue within the openquery statement in the background? or does it terminate once you get that result (SELECT TOP 1)?

Solution

  • No,SQLSERVER quits processing more rows as soon as first row is returned..

    below is simple test query to demonstrate this

    select top 1* from
openquery(testserver,'select * from performancev3.dbo.orders')

    Execution plan shows only one row is scanned and returned

    enter image description here

    SQLSERVER uses a iterative processing model .So query execution starts from root(Select operator) and it asks top operator to return one row and finally top operator asks the statement below it for one row