Take the 2-minute tour ×

Currently I have to work a bit more with SQL Server and I've read about cursors and how you should try to avoid them, because they're using a lot of resources and because they're slow.

Now I've tried to do some basic stuff with the cursor and tried to rebuild it with a while loop. At the end the cursor was ~10x faster than the while loop, while resources were about 60% to 40%.

Maybe because I have a strange example exercise?

This is about the exercise I gave myself:

  • Iterate through a table, this table has exactly one column: "Names", containing about 1000 names.
  • Print each name separately.

It's pretty easy with the cursor, but for the while-loop I need a counter or something. As the table has no index, I can't use that. So my solution was, that I created a temporary table, added all names to the table and include an index (or a row number). But the insert-operation takes about 95% of the time and at the end the while-loop is slower than the cursor.

Another thing I tried was to use a WITH Names_Rows AS... and added Row_Number() as column and then used the while loop to iterate through the Names_Rows construct. But that took even longer.

Did I miss an easy way to iterate through a table using while without having an index?

share|improve this question
Your task of looping through a list and printing each name separately is not typically performed in a database; it's usually done in the UI. We generally don't loop through things and perform operations on one record at a time. Instead, we think of things in terms of sets, and perform operations on the sets. –  DOK Dec 8 '11 at 14:27
@DOK: My little "exercise" may not be typical, but it's based on a real situation, where it was needed to loop through a table directly on the database. –  Feroc Dec 8 '11 at 14:36
Please post test code. What business problem are you trying to simulate. To get all the rows in a table you can use select * from tableName. –  Blam Dec 8 '11 at 15:00
@BalamBalam: Business problem is, that I have a function, that needs a single value. Maybe it's not the best function or the best way, but that's just the way it is at the moment. –  Feroc Dec 8 '11 at 15:33
@Feroc: It's still not clear what you are trying to achieve - do you have a single string value with 1000 comma-separated names that you are trying to split up, 1000 rows of a single value that you are trying to join up into a single comma-separated value, 1000 rows of a single value that you are trying to return separately, or what? Some sample data and output might help. –  Mark Bannister Dec 8 '11 at 15:59

2 Answers 2

up vote 4 down vote accepted

The reason for avoiding cursors is because you are performing imperative one row at a time operations rather than declarative set based operations.

Replacing a cursor with a while loop will not in itself magically make the performance better and may well make performance worse as you have found out. The difference in performance between the two will depend upon your exact code / table structures and the cursor options you choose as covered in this series of articles.

To print out all names from the table in a more set based manner you could do

DECLARE @Names nvarchar(max);

SELECT @Names = ISNULL(@Names + '
','') + Names
FROM YourTable;

PRINT @Names;
share|improve this answer
Thanks for the article, another good one from you. Your example would have, of course, the same output in a much faster way, but it wouldn't solve the original problem, as every name should get printed separately (of course it doesn't make any sense in this example). –  Feroc Dec 8 '11 at 15:35

The simplest way to execute a function against each value of a specific column in a table is:

select MyFunction(ColumnName) from TableName
share|improve this answer

Your Answer


