To Cursor or not to Cursor. That is the question.

There are many different opinions out there regarding cursor use.  Many places where they simply should NOT be used, and many places where they are the only thing that will work.  Personally, I try to avoid them at all costs.  If there is a SET based option, I will find it.  :-)    To be a little more tech savvy, I will say that it is not uncommon for cursors to be notably slower than any set based operation.  In addition to the runtime, they can also introduce table-level locking.  In my book, cursors should only be used when you truly need to access only one record at a time, and when it cannot be handled differently. 

That is what I am going to sample here -- what different methods can be used as an alternative to the cursors?  We can perform the cursor-ing without actually using a cursor.  I am using AdventureWorks v2008:

/* WHILE LOOP w/TABLE VARIABLE METHOD, INSTEAD OF A CURSOR 

   Using AdventureWorks v2008    */




  SET NOCOUNT ON;

  GO

  DECLARE @data TABLE(ID INT IDENTITY PRIMARY KEY CLUSTERED,

                     SalesOrderID INT NOT NULL)

  DECLARE

      @i INT = 1,

    @ii INT,

    @SalesOrderID INT,

    @LineCount INT;

   

    INSERT @data (SalesOrderID)

    SELECT SalesOrderID

    FROM Sales.SalesOrderHeader

    WHERE OnlineOrderFlag = 1;

  

    SET @ii = @@ROWCOUNT;

    WHILE @i <= @ii

    BEGIN

        SET @SalesOrderID = (SELECT SalesOrderID FROM @data WHERE ID = @i);

         SET @LineCount = ISNULL(@LineCount, 0) + ISNULL((

                SELECT  COUNT(*)

                FROM  Sales.SalesOrderDetail

                WHERE SalesOrderID = @SalesOrderID

                ),0);

   

        SET @i += 1;

    END

    IF @LineCount <> 60398

    BEGIN

        RAISERROR('Error',16,1);

        PRINT @LineCount;

    END

  GO




  SET NOCOUNT OFF;

       

 

Here is another example, performing a DELETE without a cursor:

   /* DELETE WITHOUT A CURSOR */

    DELETE x

    FROM dbo.YourTable x INNER JOIN (

        SELECT y.tdate, y.symbol, MAX(inserted)[Inserted]

        FROM dbo.YourTable y

        GROUP BY y.tdate, y.symbol) z

      ON x.tdate = z.tdate

      AND x.symbol = z.symbol

      AND x.inserted <> z.inserted

We can also use a CTE rather than a cursor, as well.  This is just a simple CTE method for returning the data with the oldest TradeDate without cursoring through each record:

 

/* CREATE TABLE */ 

 

  CREATE TABLE dbo.TraderOrders
  (
      OrderDate DATETIME,
      TradingGroup CHAR(10),
      TraderID INT
  );

 

  /* INSERT SAMPLE DATA */
  INSERT dbo.TraderOrders VALUES ('2012-05-24 11:06:28.080','EquityDesk','32');
  INSERT dbo.TraderOrders VALUES ('2012-05-24 11:06:28.080','FuturesDesk','32');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:14.130','FuturesDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:14.313','EquityDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:13.840','FuturesDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:13.450','EquityDesk','11');
  INSERT dbo.TraderOrders VALUES ('2012-06-08 13:30:13.050','FuturesDesk','11');
  INSERT dbo.TraderOrders VALUES ('2013-05-07 10:45:06.800','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-05-07 10:45:04.133','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-10-15 07:30:16.193','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-26 13:51:43.923','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-26 13:51:43.923','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-26 13:51:44.427','CashDesk','118');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:39.737','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:40.890','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:41.627','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 12:40:43.277','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-25 14:29:08.360','CashDesk','2');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 19:36:34.660','CashDesk','101');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 19:36:34.843','CashDesk','101');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 19:36:35.013','CashDesk','101');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 22:27:24.317','CashDesk','13');
  INSERT dbo.TraderOrders VALUES ('2013-01-21 22:31:21.443','FuturesDesk','13');
  INSERT dbo.TraderOrders VALUES ('2013-01-22 19:44:28.880','CashDesk','13');

 

  /* RETURN OLDEST TradeDate DATA per TraderID    */

   WITH CTE (OldestTradeData, TraderID)
   AS
   (
      SELECT
             MIN(TradeDate),
             TraderID
      FROM
             dbo.TraderOrders
      GROUP BY
             TraderID
    )

 

   SELECT DISTINCT to.*
   FROM
         dbo.TraderOrders to INNER JOIN CTE
           ON (CTE.OldestTradeData = to.TradeDate
           AND CTE.TraderID = to.TraderID
           AND to.VenueCode = (
                   SELECT TOP 1 TradingGroup
                    FROM
                         dbo.TraderOrders to2
                    WHERE
                         to2.TradeDate = to.TradeDate
                         AND to2.TraderID = to.TraderID
                  )
         )
  ORDER BY
        to.TraderID;