1

Imagine I have a big table with 20 columns and billion lines of data. Then I run a simple query like:

select [First Name], [Last Name]
from Audience;

After that I read the result set sequentially. Will SQL Server physically create all records (i.e. billion records) on the server side in the result set before I will start reading it? Is there any query plan that will build the result set dynamically while feeding it to the client?

I understand that concurrency reasons may prevent this. Can I give any hint that multiuser access is not possible? Maybe I should use cursors?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Buran
  • 135
  • 1
  • 11
  • 1
    The database will iterate over the records already in the table using a cursor and possibly any indices which exist. Are you suggesting that SQL Server actually makes a copy of the database for reading purposes? – Tim Biegeleisen Feb 07 '17 at 00:12
  • Yes, my question is how can I guess (when simple queries are used like the one above) when server will duplicate the data for reading purposes and when not? – Buran Feb 07 '17 at 00:15
  • For simple cases, don't think about this, think about how efficient your query strategy is. There are cases when a database will formally copy records, e.g. during an update with locking, but again for a simple `SELECT` you don't need to worry about this. – Tim Biegeleisen Feb 07 '17 at 00:17
  • @Buran, results will be streamed to the client once the last blocking operator in the execution plan completes (e.g. `SORT`). A trivial query like your example that will likely contain only a `SELECT` and `SCAN` so results will be returned immediately without intermediate storage or processing. – Dan Guzman Feb 07 '17 at 11:29

1 Answers1

3

Depends on the query plan. If the query does not require any temporary internal structures then yes you get immediate response even before the full recordset has been constructed. If the query does require temporary internal storage (e.g. you are sorting it in a manner that doesn't match any index, or an index is available but a different one is used because it requires less I/O) then you will have to wait until the full recordset is constructed.

The only way to tell is to look at the query plan and examine each and every step. You will need to know how to interpret them... for example, a DISTINCT will require a temporary structure whereas a FLOW DISTINCT will not. If the query plan shows an EAGER SPOOL you will definitely have to wait, although there are a few things you can do to avoid them.

Note: You can't rely on this-- query plans can change depending not just on schema or indexes but on database statistics (e.g. selectivity), which are always changing.

Community
  • 1
  • 1
John Wu
  • 50,556
  • 8
  • 44
  • 80