2

Is it possible to retrieve parts of the resultset from SQL Server queries in advance? For instance if I run a query that will return 20,000 rows, is it possible to throw the results in packages of 100 to the requesting instance (.net code for instance) and allow using them instead of waiting for all 20,000 rows to be read and returned?

A similar behaviour I have observed using SSMS: The resultset is filled ongoing even before the query is completed. Don't know if this is the same, but I am pretty sure if I run an SqlClient ExecuteNonQuery command, I would have to wait for all data being returned before I can fill e.g. a datagridview.

I do not know the ideal wording for this scenario, maybe this is the reason I can't find similar questions.

I have read about OPTION FAST that seems to have no effect. Does this really address the question?

3

What you describe is the default behaviour of SQL Server's query execution and client connection protocols. Rows are delivered to the server's network buffer as they are produced from an executing query plan. The network delivers them to the client, which makes them available to the application.

I think the pause you see is caused by how the application consumes the dataset returned. It's been a while, but I think SqlDataReader.Read() allows stream-like behaviour.

The risk is that the application falls behind SQL Server and the connection and server resources remain locked up until the application explicitly releases them.

0

I think you are looking for SQL Paging. Try that as a search. You can use

ROW_NUMBER() 

But I prefer the more readable OFFSET\FETCH

DECLARE @PageNum AS INT 
DECLARE @NumRows AS INT

SET @PageNum = 3
SET @NumRows = 100

SELECT ID_Column, Column1, Column2
FROM MyTable
ORDER BY ID_Column
OFFEST ((@PageNum - 1) * @NumRows) ROWS
FETCH NEXT @NumRows ROWS ONLY

The OFFSET\FETCH only works in 2012 later I believe. Use ROW_NUMBER() method if using earlier versions of SQL.

SELECT * 
FROM (
      SELECT ROW_NUMBER() OVER(ORDER BY ID_Column) AS NUM, ID_Column, Column1, Column2
      ) AS x
WHERE NUM BETWEEN ((@PageNum - 1) * @NumRows + 1) AND (@PageNum * @NumRows)
ORDER BY ID_Column
  • No, I was not looking for paging here. It's about getting the resultset like a sream, like the first row immedeately and so on without waiting for the whole answer to complete. – Magier Jan 21 '16 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.