What is the best way to loop over a table's rows?

Question

Hi,

What is the best way to loop over a database table's rows in SQL Server 2008 R2?

I am looking for something which is fairly similar to writing foreach ( ) and quite performant.

Thanks

Answer 1

Best performing: don't loop over a table's rows. Use set-based operations.

Here's a good discussion of WHY

Answer 2

Perhaps you want a CURSOR? See SQL Server Cursor Examples (also provides links and discusses issues/alternatives/reasons). It may or may not meet the definition of 'best' -- SQL DQL really does like to work with 'set operations' (and this is what it's designed to do, there are some caveats with using curors).

Happy coding.

Edit (for marc_s): You might want a cursor (particularly if generating some SQL dynamically -- ick, but still). The reason for this operation was not specified and thus the answer above tries to be neutral (although the wording and the information in the link do cast a shadow on the use of cursors in general). While cursors do have some issues (and really don't fit will with other DQL constructs), they may be the best (or only) way to perform certain operations.