Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Loops vs. Cursors

Cursors = Old School and Bad (It Depends!)

After reading through some site comments, I decided to get to the bottom of this issue: Are cursors really bad? Well... it depends. Cursors are bad if you are running SQL 2000 and SQL 2005 prior to the SP2 update. Since then, they have been optimized to run just as well as a WHILE loop. Peronally, I work on servers from 2000 to 2008 and I want to keep things standardized across the versions, so I just use WHILE loops to cycle through rows. Below we will go over the implementation of WHILE loops so you can compare previous and past rows to the current one.

One thing I will say about looping in general, try not to do it if you don't have too. The best option would be to perform your calculations on all of the rows at once.

Let's go over the implementation of loops.



Cycling Through a Table 1 Row at a Time

If you ever find yourself in a spot for cycling through rows, here is how you do it:

1. Create a temp table with an identity column that auto-increments, starting at 1 and going up by 1.
  CREATE TABLE #Temp1 (
    ID INT Identity(1,1),
    ItemName VARCHAR(50),
    ItemDescription VARCHAR(50)
  )

2. Get the data inserted for review (you do not need to insert the ID field, it is being inserted with numbers increasing by 1 and starting at 1).
  INSERT INTO #Temp1(
    ItemName,
    ItemDescription
  )
  SELECT ItemName,
    ItemDescription
  FROM InventoryTable

3. Create the loop and cycle through the data.
  DECLARE @Counter INT
  SET @Counter = 1

  DECLARE @ItemReview VARCHAR(50)

  WHILE @Counter <= (SELECT COUNT(*) FROM InventoryTable)
  BEGIN
    SELECT @ItemReview = t.ItemName
    FROM #Temp1 t
    WHERE t.ID = @Counter

    IF @ItemReview = 'Tomatoes'
    BEGIN
        Do something hereā€¦.
    END

    SET @Counter = @Counter + 1

  END

Notice how we use a @Counter variable to cycle through the rows on the table by matching up against the ID field that was auto-incremented when we inserted the data. This allows us to go through every row on the table and find the word "Tomatoes".





Comments

Name:
Title:   
Commenting Currently Disabled



1/9/2013 10:38:08 AM



btw
1/7/2013 4:53:04 AM
dhr.


perfect. tx.

12/14/2012 1:18:16 PM




12/14/2012 7:59:11 AM



noa
9/19/2011 5:38:00 AM
loops


diffrence between coursers and loops
Carly
8/3/2011 6:31:37 PM



Wait, I cannot fahtom it being so straightforward.
Jason
7/11/2011 9:14:26 PM
Set vs. Loop


Hi Jon, I definitely agree set based code is much faster. This is just a loop example for someone who can't figure out how to write the set based version and hopefully they are not dealing with too many rows either.
jon
7/11/2011 7:53:15 AM



Hi Jason, thank you for taking the time to respond. I would be happy to provide some optmized set based code but it is not really clear what your query is doing. Anyway my point was really that a while loop is no better than a cursor, but it seems that you have change the article slightly since i last checked in.
Jason
7/10/2011 3:48:48 PM
Yeah, that was supposed to be SP 2


Typo, changed to SP2
Gail Shaw
7/10/2011 10:41:26 AM
SQL 2005 R2???


No such version. There's 2005, 2008 and 2008 R2.
Jason
7/7/2011 5:06:56 PM
Page Revision


Thank you guys for all the comments! I decided to get to the bottom of this issue (and now myth for me) about cursors. There are a vast amount of posts about them being bad, and so few about them being good. So instead of reading what people say, I did a review of SQL 2000, 2005, and 2008 on Microsoft's website and looked through all of the bug fixes. This was just an issue prior to 2005 R2. I have revised this page and ahve joined the Cursors Are Good (but try not to loop in general) camp.
Gail Shaw
7/7/2011 3:56:45 PM
Known bug


Got a reference for that known memory leak? Is Microsoft planning to fix it in a future version?
Jeff Moden
7/7/2011 7:48:27 AM



A fast_forward, read_only, static cursor is just as effective as a While Loop and uses the same resources because they're both using a Temp table (as in your example). It's a myth that Cursors are worse than While Loops especially with the "settings" I mentioned above. The real key is to not use any form of RBAR if at all possible (and it's usually possible).
Jason
7/6/2011 12:06:03 PM
Cursors


Hi Jon - I think it really depends on what you need to do. This is a straight forward example that will let you compare previous rows and future rows to perform calculations, while saving them off to the current row. I have actually used this type of logic in replacement of a cursor and saw a query go from over 5 minutes to under 1 minute. I guess it depends on what you need to do in the end. If there is an alternative situation you are thinking of, please feel free to email me the solution and I will post a section in this article with your name attached (if you want). Contact information is at the top.
jon
7/6/2011 2:23:00 AM
Cursors


You say that cursors are bad, then go on to show some code that is just as bad if not worse that a cursor.
Jason
7/5/2011 1:09:35 PM
MS SQL Server vs. Oracle


Great comment. I have updated the main page to be more clear that this is aimed at MS SQL Server.

7/5/2011 2:54:38 AM



You might want to make it clear that this is SQL Server specific. Nowhere does it say that, and in fact is implying (to me) it's aimed at any SQL database, at least until I read it. The bit on query plans makes it abundantly clear!
Jason
7/4/2011 11:12:19 AM
Cursors and SQL


It is definitely a comment limited to SQL Server. I don't have much experience with Oracle and wrote this post with SQL Server in mind.

7/3/2011 9:41:22 PM



It's usually better to use a set if you can but "cursors are really bad" is not true for Oracle. They're fine, that limitation is SQL Server & maybe Sybase specific.