Default Result Sets

Microsoft® SQL Server™ sends result sets back to clients in the following way:

1. SQL Server receives a network packet from the client containing the Transact-SQL statement or batch of Transact-SQL statements to be executed.
2. SQL Server compiles and executes the statement or batch.
3. SQL Server begins putting the rows of the result set (or multiple result sets from a batch or stored procedure) in network packets and sending them to the client. SQL Server puts as many result set rows as possible in each packet.
4. The packets containing the result set rows are cached in the network buffers of the client. As the client application fetches the rows, the ODBC driver, OLE DB provider, or DB-Library dynamic-link library (DLL) pulls the rows from the network buffers and transfers the data to the client application. The client retrieves the results one row at a time in a forward direction.

The client cannot send any other Transact-SQL statements on that connection until the application has either processed all the rows returned by SQL Server or sent SQL Server a request to cancel the rest of the results. No updates can be done on the connection until all the results have been processed; the result sets are read-only.

This is the type of result set processing SQL Server uses when no cursors have been requested. This happens when the following conditions are met:

• The application does not use the DECLARE CURSOR statement to request a Transact-SQL server cursor. The application instead executes the Transact-SQL statements, such as SELECT, directly.
• If the application uses ADO, OLE DB, and ODBC, it leaves all API cursor attributes at their default settings so that no API cursors are requested. This default set of attributes is to request a forward-only, read-only cursor with a rowset size of 1.
• If the application uses DB-Library, it uses the DB-Library core functions to execute the statement and process the result set.

Because this type of processing is used when all cursor attributes are set to their defaults, and when no cursor processing is actually involved from SQL Server or the database API, this is called a default result set.

A default result set is not given to an application in one large block. The result set is cached in the network buffers on the client. The application fetches through the result set one row at a time. On each fetch, the OLE DB provider, ODBC driver, or DB-Library DLL moves the data from the next row in the network buffer into variables in the application. OLE DB, ODBC, and ADO applications use the same API functions to retrieve the rows that they would use to fetch the rows from a cursor. DB-Library applications use the core function dbnextrow to fetch each row.

Default result sets are the most efficient way to transmit results to the client. The only packet sent from the client computer to the server is the original packet with the statement to execute. When the results are sent back to the client, SQL Server puts as many result set rows as it can into each packet, minimizing the number of packets sent to the client.

All Transact-SQL statements are supported when using default result sets. You can also execute batches or stored procedures containing multiple statements that return result sets when using default result sets.

Default result sets can only be used to send result sets back to a client application. The data in a default result set is not available to any other Transact-SQL statement or variable in a batch, stored procedure, or trigger. For example, consider this statement in a stored procedure or trigger:

SELECT ProductID FROM Northwind.dbo.Products

The statement generates a default result set containing the IDs of all the products in the Northwind database. None of the other Transact-SQL statements or variables in the stored procedure or trigger can reference this list of product IDs. The only thing that is done with this result set is that SQL Server sends it to the client. In order for the data in this result set to be used by other Transact-SQL statements, it would have to be in a Transact-SQL server cursor:

DECLARE abc CURSOR FOR

SELECT ProductID FROM Northwind.dbo.Products

See Also

Using Default Result Sets