When query using JDBC createStatement and getting ResultSet of millions of rows (from SQL Server), what SQL queries are really executed, where do the rows are stored and how many rows? Does it use SQL Server cursor? I know that only a limited number of rows are kept in the client-side memory (where the Java code), so where are the rest of the rows kept, or maybe they are not yet queried from the SQL Server)? When I looked at SQL Server profiler, it just shows the normal query (it didn't add cursor or TOP X of something else)
Taken from the SQL Server documentation on Adaptive Buffering:
Normally, when the Microsoft JDBC Driver for SQL Server executes a query, the driver retrieves all of the results from the server into application memory. Although this approach minimizes resource consumption on the SQL Server, it can throw an OutOfMemoryError in the JDBC application for the queries that produce very large results.
There's a way around that, what SQL Server calls Adaptive Buffering and which seems to be enabled by default for JDBC driver version 2+ and you might want to read up on it in the documentation. Here's the gist:
In order to allow applications to handle very large results, the Microsoft JDBC Driver for SQL Server provides adaptive buffering. With adaptive buffering, the driver retrieves statement execution results from the SQL Server as the application needs them, rather than all at once.