Firehose cursors are identified in Microsoft SQL Server as the default result set. A default result set is generated when the statement attributes of a cursor are left unchanged from their standard MS SQL defaults. The default result set allows rows from a query result to be pulled without locks in forward-only sequence into a client-side cache. The default result set is referred to as a firehose cursor because it can "flood" the client with results. It is unencumbered by the cursor management necessary with server-side cursors.
The following DataServer operations are eligible for the firehose cursor implementation:
All NO-LOCK queries.
All SHARE-LOCK queries with transaction isolation level set to read-uncommitted.
Internal no-lock queries that populate the key cache for transaction-oriented operations.
All stored procedure result sets.
All send-sql-statement result sets.
Queries written with the QUERY-TUNING(SEPARATE-CONNECTION) keyword. When connection pooling is enabled, the QUERY-TUNING(SEPARATE-CONNECTION) is redundant.
Note: Prepared statements associated with firehose cursors are cached on a statement cache that is associated with the managed connection. Statement reuse may decrease based on the recycling of managed connections. To completely disable the prepared statement cache, use the following connection switch: "-Dsrv PRGRS_PREPCACHE,0". For information about monitoring the statement cache reuse, see Monitoring cursor and connection use.
All NO-LOCK queries.