Based on the location of a cursor creation, cursors can be classified into two categories:

The T-SQL cursors are always created on SQL Server. However, the database API cursors can be created on either the client side or the server side.

Client-Side Cursors

As its name signifies, a client-side cursor is created on the machine running the application, whether the app is a service, a data access layer, or the front end for the user. It has the following characteristics:

Server-Side Cursors

A server-side cursor is created on the SQL Server machine. It has the following characteristics:

Depending on the required degree of isolation and synchronization with the underlying content, cursors can be classified into the following concurrency models:

Read-Only

A read-only cursor is nonupdatable; no locks are held on the base table(s). While fetching a cursor row, whether an (S) lock will be acquired on the underlying row or not depends upon the isolation level of the connection and the locking hint used in the SELECT statement for the cursor. However, once the row is fetched, by default the locks are released.

The following T-SQL statement creates a read-only T-SQL cursor:

DECLARE MyCursor CURSOR READ_ONLY
    FOR SELECT  adt.Name
        FROM    Person.AddressType AS adt
        WHERE   adt.AddressTypeID = 1

The lack of locking makes the read-only type of cursor faster and safer. Just remember that you cannot manipulate data through the read-only cursor, which is the sacrifice you make for performance.

Optimistic

The optimistic with values concurrency model makes a cursor updatable. No locks are held on the underlying data. The factors governing whether an (S) lock will be acquired on the underlying row are the same as for a read-only cursor.

The optimistic concurrency model uses row versioning to determine whether a row has been modified since it was read into the cursor instead of locking the row while it is read into the cursor. Version-based optimistic concurrency requires a TIMESTAMP column in the underlying user table on which the cursor is created. The TIMESTAMP data type is a binary number that indicates the relative sequence of modifications on a row. Each time a row with a TIMESTAMP column is modified, SQL Server stores the current value of the global TIMESTAMP value, @@DBTS, in the TIMESTAMP column and then increments the @@DBTS value.

Before applying a modification through the optimistic cursor, SQL Server determines whether the current TIMESTAMP column value for the row matches the TIMESTAMP column value for the row when it was read into the cursor. The underlying row is modified only if the TIMESTAMP values match, indicating that the row hasn't been modified by another user in the meantime. Otherwise, an error is raised. In case of an error, first refresh the cursor with the updated data.

If the underlying table doesn't contain a TIMESTAMP column, then the cursor defaults to value-based optimistic concurrency, which requires matching the current value of the row with the value when the row was read into the cursor. The version-based concurrency control is more efficient than the value-based concurrency control since it requires less processing to determine the modification of the underlying row. Therefore, for the best performance of a cursor with the optimistic concurrency model, ensure that the underlying table has a TIMESTAMP column.

The following T-SQL statement creates an optimistic T-SQL cursor:

DECLARE MyCursor CURSOR OPTIMISTIC
    FOR SELECT  adt.Name
        FROM    Person.AddressType AS adt
        WHERE   adt.AddressTypeID = 1

Scroll Locks

A cursor with scroll locks concurrency holds a (U) lock on the underlying row until another cursor row is fetched or the cursor is closed. This prevents other users from modifying the underlying row when the cursor fetches it. The scroll locks concurrency model makes the cursor updatable.

The following T-SQL statement creates a T-SQL cursor with the scroll locks concurrency model:

DECLARE MyCursor CURSOR SCROLL_LOCKS
    FOR SELECT  adt.Name
        FROM    Person.AddressType AS adt
        WHERE   adt.AddressTypeID = 1

Since locks are held on the underlying rows (until another cursor row is fetched or the cursor is closed), it blocks all the other users trying to modify the row during that period. This hurts database concurrency.

Cursors can be classified into the following four types:

Let's take a closer look at these four types in the sections that follow.

Forward-Only Cursors

These are the characteristics of forward-only cursors:

The forward-only characteristic is implemented differently by the database API cursors and the T-SQL cursor. The data access layers implement the forward-only cursor characteristic as one of the four previously listed cursor types. But the T-SQL cursor doesn't implement the forward-only cursor characteristic as a cursor type; rather, it implements it as a property that defines the scrollable behavior of the cursor. Thus, for a T-SQL cursor, the forward-only characteristic can be used to define the scrollable behavior of one of the remaining three cursor types.

A forward-only cursor with a read-only property can be created using a fast_forward statement. The T-SQL syntax provides a specific cursor type option, FAST_FORWARD, to create a fast-forward-only cursor. The nickname for the FAST_FORWARD cursor is the fire hose because it is the fastest way to move data through a cursor and because all the information flows one way. The following T-SQL statement creates a fast-forward-only T-SQL cursor:

DECLARE MyCursor CURSOR FAST_FORWARD
    FOR SELECT  adt.Name
        FROM    Person.AddressType AS adt
        WHERE   adt.AddressTypeID = 1

The FAST_FORWARD property specifies a forward-only, read-only cursor with performance optimizations enabled.

Static Cursors

These are the characteristics of static cursors:

The following T-SQL statement creates a static T-SQL cursor:

DECLARE MyCursor CURSOR STATIC
    FOR SELECT  adt.Name
        FROM    Person.AddressType AS adt
        WHERE   adt.AddressTypeID = 1

Some tests show that a static cursor can perform as well as, and sometimes faster than, a forward-only cursor. Be sure to test this behavior on your own system.

Keyset-Driven Cursors

These are the characteristics of keyset-driven cursors:

The following T-SQL statement creates a keyset-driven T-SQL cursor:

DECLARE MyCursor CURSOR KEYSET
    FOR SELECT  adt.Name
        FROM    Person.AddressType AS adt
        WHERE   adt.AddressTypeID = 1

Dynamic Cursors

These are the characteristics of dynamic cursors:

The following T-SQL statement creates a dynamic T-SQL cursor:

DECLARE MyCursor CURSOR DYNAMIC
    FOR SELECT  adt.Name
        FROM    Person.AddressType AS adt
        WHERE   adt.AddressTypeID = 1

The dynamic cursor is absolutely the slowest possible cursor to use in all situations. Take this into account when designing your system.

The client-side and server-side cursors have their own cost benefits and overhead, as explained in the sections that follow.

Client-Side Cursors

Client-side cursors have the following cost benefits compared to server-side cursors:

Client-side cursors have the following cost overhead or drawbacks:

Server-Side Cursors

Server-side cursors have the following cost benefits:

Server-side cursors have the following cost overhead or disadvantages:

As expected, cursors with a higher concurrency model create the least amount of blocking in the database and support higher scalability, as explained in the following sections.

Read-Only

The read-only concurrency model provides the following cost benefits:

The main drawback of the read-only cursor is as follows:

Optimistic

The optimistic concurrency model provides the following benefits:

The following are the cost overhead of the optimistic concurrency model:

Scroll Locks

The major benefit of the scroll locks concurrency model is as follows:

The scroll locks concurrency model incurs the following cost overhead:

Each of the basic four cursor types mentioned in the "Cursor Fundamentals" section earlier in the chapter incurs a different cost overhead on the server. Choosing an incorrect cursor type can hurt database performance. Besides the four basic cursor types, a fast-forward-only cursor, a variation of the forward-only cursor, is provided to enhance performance. The cost overhead of these cursor types is explained in the sections that follow.

Forward-Only Cursors

These are the cost benefits of forward-only cursors:

The forward-only cursor type has the following drawbacks:

Fast-Forward-Only Cursor

The fast-forward-only cursor is the fastest and least expensive cursor type. This forward-only and read-only cursor is specially optimized for performance. Because of this, you should always prefer it to the other SQL Server cursor types.

Furthermore, the data access layer provides a fast-forward-only cursor on the client side, making the cursor overhead almost disappear by using a default result set.

Static Cursors

These are the cost benefits of static cursors:

On the downside, the static cursor has the following cost overhead:

Keyset-Driven Cursors

These are the cost benefits of keyset-driven cursors:

The cost overhead of keyset-driven cursors is as follows:

Dynamic Cursor

The dynamic cursor has the following cost benefits:

The dynamic cursor has the following cost overhead:

The default result set is generally the best and most efficient way of returning rows from SQL Server for the following reasons:

Multiple Active Result Sets

SQL Server 2005 introduced the concept of multiple active result sets (MARS) 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.

When connecting through ODBC, to enable MARS, include SQL_COPT_SS_MARS_ENABLED = SQL_MARS_ENABLED_YES as part of the connection properties. When using an OLEDB connection, you have to set the following property and value: SSPROP_INIT_MARSCONNECTION =VARIANT_TRUE.

While there are advantages to the default result set, there are drawbacks as well. Using the default result set requires some special conditions for maximum performance:

To demonstrate these concepts, consider the following test table (create_t1.sql in the download):

USE AdventureWorks2008 ;
GO
IF (SELECT  OBJECT_ID('dbo.t1')
   ) IS NOT NULL
    DROP TABLE dbo.t1;
GO
CREATE TABLE dbo.t1 (c1 INT, c2 CHAR(996));
CREATE CLUSTERED INDEX i1 ON dbo.t1 (c1);
INSERT  INTO dbo.t1
VALUES  (1, '1'),
INSERT  INTO dbo.t1
VALUES  (2, '2'),
GO

Consider a web page accessing the rows of the test table using ADO with OLEDB, with the default cursor type for the database API cursor (ADODB.Recordset object) as follows (default_cursor.asp in the download):

<%
Dim strConn, Conn, Rs
    Conn = CreateObject("ADODB.Connection")
    strConn = "Provider=SQLOLEDB;" _
              & "Data Source=FRITCHEYGXPGF2008;" _
              & "Initial Catalog=AdventureWorks2008;" _
              & "Integrated Security=SSPI; Persist Security Info=False;"
    Conn.Open(strConn)
    Rs = CreateObject("ADODB.Recordset")
'Declare & open a database API cursor with default settings
' (forward-only, read-only are the default settings)
    Rs.Open("SELECT * FROM t1", Conn)
'Consume the rows in the cursor one row at a time
While Not Rs.EOF
  'Fetch a row from the cursor
        Response.Write("c1 = " & Rs.Fields("c1").Value & "<BR>")
  Rs.MoveNext
    End While

'Close the cursor and release all resources assigned to the
'cursor
Rs.Close
    Rs = Nothing
Conn.Close
    Conn = Nothing
%>

Note that the table has two rows with the size of each row equal to 1,000 bytes (= 4 bytes for INT + 996 bytes for CHAR(996)) without considering the internal overhead. Therefore, the size of the complete result set returned by the SELECT statement is approximately 2,000 bytes (= 2 × 1,000 bytes).

You can execute the code of the preceding web page step-by-step using Microsoft Visual Studio .NET. On execution of the cursor open statement (Rs.Open), a default result set is created on the client machine running the code. The default result set holds as many rows as the client-network buffer can cache.

Since the size of the result set is small enough to be cached by the client-network buffer, all the cursor rows are cached on the client machine during the cursor open statement itself, without retaining any lock on table t1. You can verify the lock status for the connection using the sys.dm_tran_locks dynamic management view. During the complete cursor operation, the only request from the client to SQL Server is the SELECT statement associated to the cursor, as shown in the Profiler output in Figure 14-1.

Figure 14.1. Profiler trace output showing database requests made by the default result set

To find out the effect of a large result set on the default result set processing, let's add some more rows to the test table (addrows.sql in the download):

--Add 100000 rows to the test table
SELECT TOP 100000
        IDENTITY( INT,1,1 ) AS n
INTO    #Tally
FROM    Master.dbo.SysColumns sc1
       ,Master.dbo.SysColumns sc2 ;

INSERT  INTO t1
        SELECT  n
               ,n
        FROM    #Tally AS t ;
GO

This increases the size of the result considerably. Depending on the size of the client-network buffer, only part of the result set can be cached. On execution of the Rs.Open statement, the default result set on the client machine will get part of the result set, with SQL Server waiting on the other end of the network to send the remaining rows.

On my machine, during this period, the locks shown in Figure 14-2 are held on the underlying table t1 as obtained from the output of sys.dm_tran_locks.

Figure 14.2. sys.dm_tran_locks output showing the locks held by the default result set while processing the large result set

The (IS) lock on the table will block other users trying to acquire an (X) lock. To minimize the blocking issue, follow these recommendations:

The T-SQL cursors implemented using T-SQL statements are always executed on SQL Server, since they need the SQL Server engine to process their T-SQL statements. You can use a combination of the cursor characteristics explained previously to reduce the overhead of these cursors. As mentioned earlier, the most lightweight T-SQL cursor is the one created not with the default settings but by manipulating the settings to arrive at the forward-only read-only cursor. That still leaves the T-SQL statements used to implement the cursor operations to be processed by SQL Server. The complete load of the cursor is supported by SQL Server without any help from the client machine. To analyze the overhead of T-SQL cursors on SQL Server, suppose an application requirement consists of the following functionalities:

The "For each" phrase in the second point suggests that these application requirements could be served by a cursor. You can implement this application requirement using a T-SQL cursor as follows (app_requirements.sql in the download):

IF (SELECT  OBJECT_ID('dbo.spTotalLoss_CursorBased')
   ) IS NOT NULL
    DROP PROC dbo.spTotalLoss_CursorBased ;
GO
CREATE PROC dbo.spTotalLoss_CursorBased
AS --Declare a T-SQL cursor with default settings, i.e., fast
--forward-only to retrieve products that have been discarded
    DECLARE ScrappedProducts CURSOR
        FOR SELECT  p.ProductID
                   ,wo.ScrappedQty
                   ,p.ListPrice
            FROM    Production.WorkOrder AS wo
                    JOIN Production.ScrapReason AS sr
                    ON wo.ScrapReasonID = sr.ScrapReasonID
                    JOIN Production.Product AS p
                    ON wo.ProductID = p.ProductID ;
--Open the cursor to process one product at a time
    OPEN ScrappedProducts ;

    DECLARE @MoneyLostPerProduct MONEY = 0
       ,@TotalLoss MONEY = 0 ;

--Calculate money lost per product by processing one product
--at a time
    DECLARE @ProductId INT
       ,@UnitsScrapped SMALLINT
       ,@ListPrice MONEY ;
    FETCH NEXT FROM ScrappedProducts INTO @ProductId, @UnitsScrapped,
        @ListPrice ;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @MoneyLostPerProduct = @UnitsScrapped * @ListPrice ;
  --Calculate total loss
            SET @TotalLoss = @TotalLoss + @MoneyLostPerProduct ;
            FETCH NEXT FROM ScrappedProducts INTO @ProductId, @UnitsScrapped,
                @ListPrice ;
        END

--Determine status
    IF (@TotalLoss > 5000)
        SELECT  'We are bankrupt!' AS Status ;
    ELSE
        SELECT  'We are safe!' AS Status ;

--Close the cursor and release all resources assigned to the cursor
    CLOSE ScrappedProducts ;
    DEALLOCATE ScrappedProducts ;
GO

The stored procedure can be executed as follows, but execute it twice to take advantage of plan caching:

EXEC dbo.spTotalLoss_CursorBased

Figure 14-3 shows the Profiler trace output for this stored procedure.

Figure 14.3. Profiler trace output showing the total cost of the data processing using a T-SQL–based cursor

As you can see in Figure 14-3, lots of statements are executed on SQL Server. Essentially, all the SQL statements within the stored procedure are executed on SQL Server, with the statements within the WHILE loop executed several times (one for each row returned by the cursor's SELECT statement).

The total number of logical reads performed by the stored procedure is 8,788 (indicated by the last SQL:BatchCompleted event). Well, is it high or low? Considering the fact that the Production.Products table has only 13 pages and the Production.WorkOrder table has only 524, it's surely not low. You can determine the number of pages allocated to these tables by querying the dynamic management view sys.dm_db_index_physical_stats:

SELECT  SUM(page_count)
FROM    sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008'),
                                       OBJECT_ID('Production.WorkOrder'),
                                       DEFAULT, DEFAULT, DEFAULT)

In most cases, you can avoid cursor operations by rewriting the functionality using SQL queries, concentrating on set-based methods of accessing the data. For example, you can rewrite the preceding stored procedure using SQL queries (instead of the cursor operations) as follows (no_cursor.sql in the download):

IF (SELECT  OBJECT_ID('dbo.spTotalLoss')
   ) IS NOT NULL
    DROP PROC dbo.spTotalLoss;
GO
CREATE PROC dbo.spTotalLoss
AS
    SELECT  CASE --Determine status based on following computation
                 WHEN SUM(MoneyLostPerProduct) > 5000 THEN 'We are bankrupt!'
                 ELSE 'We are safe!'
            END AS Status
    FROM    (--Calculate total money lost for all discarded products
             SELECT SUM(wo.ScrappedQty * p.ListPrice) AS MoneyLostPerProduct
             FROM   Production.WorkOrder AS wo
                    JOIN Production.ScrapReason AS sr
                    ON wo.ScrapReasonID = sr.ScrapReasonID
                    JOIN Production.Product AS p
                    ON wo.ProductID = p.ProductID
             GROUP BY p.ProductID
            ) DiscardedProducts;
GO

In this stored procedure, the aggregation functions of SQL Server are used to compute the money lost per product and the total loss. The CASE statement is used to determine the business status based on the total loss incurred. The stored procedure can be executed as follows, but again, do it twice so that you can see the results of plan caching:

EXEC dbo.spTotalLoss

Figure 14-4 shows the corresponding Profiler trace output.

Figure 14.4. Profiler trace output showing the total cost of the data processing using an equivalent SELECT statement

From Figure 14-4, you can see that the second execution of the stored procedure, which reuses the existing plan, uses a total of 543 logical reads; however, even more importantly than the reads, the CPU drops from 94 in Figure 14-3 to 16 in Figure 14-4 and the duration goes from 696 ms to 20 ms. Using SQL queries instead of the cursor operations made the duration 34.8 times faster.

Therefore, for better performance, it is almost always recommended that you use set-based operations in SQL queries instead of T-SQL cursors.