Default Result Set Processing and Multiple Active Result Sets
When a request is submitted for execution, SQL Server sends result sets back to clients in the following way:
SQL Server receives a network packet from the client containing the Transact-SQL statement or batch of Transact-SQL statements to be executed.
SQL Server compiles and executes the statement or batch.
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.
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 or the OLE DB provider 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.
This mode of returning results is known as a default result set.
SQL Server provides the ability to have multiple active result sets under a single connection, which is commonly referred as MARS. This capability must be enabled explicitly per connection. It is only available in the SQL Server Native Client ODBC driver, the SQL Server Native Client OLEDB provider, and the .NET Framework 2.0 SqlClient Managed Provider.
A connection string attribute or session property is used to enable MARS for a particular session:
For ODBC, SQL_COPT_SS_MARS_ENABLED should be set to SQL_MARS_ENABLED_YES
For OLEDB, SSPROP_INIT_MARSCONNECTION should be set to VARIANT_TRUE
For SqlClient managed provider, the connection string attribute MultipleActiveResultSets must be set to true.
MARS is only available for client providers that are connected to SQL Server. MARS is not available in any combination of earlier releases of the client providers or SQL Server.
In earlier releases of SQL Server or in sessions without MARS enabled, the client cannot send any other requests on a particular 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.
"Firehose cursor" is an obsolete term for default result sets.
Default Result Set 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.
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 or the ODBC driver 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. The SqlClient managed provider uses the SqlDataReader class to expose a default result set. When MultipleActiveResultSets is set to true, more than one SqlDataReader is allowed to be open at a given time.
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 the following statement in a stored procedure or trigger:
SELECT ProductID FROM AdventureWorks2008R2.Production.Product;
The statement generates a default result set containing the IDs of all the products in the AdventureWorks2008R2 database, which is sent directly to the client by SQL Server. None of the other Transact-SQL statements or variables in the stored procedure or trigger can reference this list of product IDs. 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 AdventureWorks2008R2.Production.Product;