While Loop in T-SQL

Question

While Loop in T-SQL

Sukhdevsinh Dhummad

SSC Enthusiast

Points: 117
More actions
October 15, 2024 at 2:33 pm

#4468808

Comments posted to this topic are about the item While Loop in T-SQL

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76979 More actions · Answer 1

You state that, "Often, we encounter situations where we need to loop through a dataset to process or update records iteratively." However, the sample that you use can trivially be converted to a simple update statement without resorting to a loop. You need to use a problem that is complex enough that it doesn't have a straightforward solution without using a loop. You've failed to prove the thesis that we EVER need to loop through a dataset, much less the stronger thesis that this is common.

Also, you've manually recreated a CURSOR, but failed to compare your approach to using a CURSOR. Does your approach perform better or worse than a CURSOR? I suspect that a cursor will actually perform ever so slightly better here, because the parser knows how to optimize cursors.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 2

I totally agree with Drew's post. You haven't used an example that even comes close to justifying the use of such RBAR. This could cause folks, especially beginners, to make some very poor decisions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sukhdevsinh Dhummad SSC Enthusiast Points: 117 More actions · Answer 3

Thank you for your detailed feedback. I appreciate your thoughts on the sample used and the suggestion to use a more complex problem that justifies looping. I’ll consider updating the example to better illustrate cases where looping is necessary.

Regarding the comparison with a CURSOR, you're right; this is an important aspect that I should have addressed. I’ll run performance comparisons and update the article accordingly to help readers make informed decisions. The only reason I took above example so that it was easy to understand, did not mean to confuse anyone to choose loop over set operation. Set operations should be the way to go.

Thanks again for helping improve the article.