fetch resultset in chunks in SQL Server

Let's say I have a query that return 10000 rows. Is it possible to configure this query in SQL Server so that it returns the result in chunks with 1000 rows each? So my application receives 1000 rows, processes them, and while it does that SQL Server is working to send the next 1000 r开发者_开发百科ows etc.?

This is SQL Server 2005.

---

SQL Server gives you a result set, which is delivered as a network packet stream (assuming you use TCP/IP to connect to the server).

So if you want to be really hardcore, you can implement the wire protocol to access SQL Server over the net and then work on the TCP stream. This would be a major work however and is not really advisable.

Another possibility would be to query not once, but to use several queries, where each one gets a certain "window" of your resultset. This is called "server side paging". See this article for a description of how it's done.

Another method would be to use a stream based access on the client side. For example, if you use ADO.NET as client, you can use the SqlDataReader to get one row at a time (in the background the data is still delivered in chunks over the network, but that is transparent to you). But that depends on the client you use, which you have not specified.