In a previous TechRepublic column about SQL Server 2005, I showed how you can convert some of your iterative queries (cursors) to set-based operations in order to enhance performance.
You should take this approach in most circumstances, as you'll potentially see performance gains when switching from a looping construct to a set-based construct.
There are situations in which you need to individually handle each row in a result set. SQL Server 2008 provides a minimal set of tools — cursors and WHILE loops — to accomplish this task. I'll take a closer look at each option and explain why it's difficult to pinpoint which is better in terms of performance.
Cursors
Cursors are a looping construct built inside the database engine and come with a wide variety of features. Cursors allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. Depending on the type of cursor you request, you can even fetch records that you've previously fetched.
See the rest of the article on TechRepublic.
This was published in 
1
Colby - 12/12/09
while version is slow because it results in a table scan for each loop through the 1000 (N!). Try adding an index to the table make sure the query plan uses the index and now the IO will drop dramatically and now uses an index seek (no table scan)
» Report offensive content