Database Reference
In-Depth Information
Default Result Set
The default cursor type for the data access layers (ADO, OLEDB, and ODBC) is forward-only and read-only. The
default cursor type created by the data access layers isn't a true cursor, but a stream of data from the server to the
client, generally referred to as the
default result set
or fast-forward-only cursor (created by the data access layer).
In ADO.NET, the
DataReader
control has the forward-only and read-only properties, and it can be considered as the
default result set in the ADO.NET environment. SQL Server uses this type of result set processing under the
following conditions:
•
The application, using the data access layers (ADO, OLEDB, ODBC), leaves all the cursor
characteristics at the default settings, which requests a forward-only and read-only cursor.
SELECT
statement instead of executing a
DECLARE CURSOR
•
The application executes a
statement.
■
because sQL server is designed to work with sets of data, not to walk through records one by one, the default
result set is always faster than any other type of cursor.
Note
The only request sent from the client to SQL Server is the SQL statement associated with the default cursor.
SQL Server executes the query, organizes the rows of the result set in network packets (filling the packets as best it
can), and then sends the packets to the client. These network packets are cached in the network buffers of the client.
SQL Server sends as many rows of the result set to the client as the client-network buffers can cache. As the client
application requests one row at a time, the data access layer on the client machine pulls the row from the
client-network buffers and transfers it to the client application.
The following sections outline the benefits and drawbacks of the default result set.
Benefits
The default result set is generally the best and most efficient way of returning rows from SQL Server for the
following reasons:
•
Minimum network round-trips between the client and SQL Server:
Since the result set returned
by SQL Server is cached in the client-network buffers, the client doesn't have to make a request
across the network to get the individual rows. SQL Server puts most of the rows that it can in
the network buffer and sends to the client as much as the client-network buffer can cache.
•
Minimum server overhead:
Since SQL Server doesn't have to store data on the server, this
reduces server resource utilization.
Multiple Active Result Sets
SQL Server 2005 introduced the concept of multiple active result sets, wherein a single connection can have more
than one batch running at any given moment. In prior versions, a single result set had to be processed or closed
out prior to submitting the next request. MARS allows multiple requests to be submitted at the same time through
the same connection. MARS is enabled on SQL Server all the time. It is not enabled by a connection unless that
connection explicitly calls for it. Transactions must be handled at the client level and have to be explicitly declared
and committed or rolled back. With MARS in action, if a transaction is not committed on a given statement and
the connection is closed, all other transactions that were part of that single connection will be rolled back. MARS is
enabled through application connection properties.
