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

What is the best way to loop over a datab开发者_运维问答ase table's rows in SQL Server 2008 R2?

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

Thanks

---

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

Here's a good discussion of WHY

---

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.

---

While looping through records in T-SQL is not recommended for production code, there is occasional need for such. A couple great examples can be found on Microsoft's Support site (none of which use cursors): http://support.microsoft.com/kb/111401

And here is a list of the methods:

One method is the use of temp tables. With this method, you create a "snapshot" of the initial SELECT statement and use it as a basis for "cursoring." For example:

/********** example 1 **********/ 

declare @au_id char( 11 )

set rowcount 0
select * into #mytemp from authors

set rowcount 1

select @au_id = au_id from #mytemp

while @@rowcount <> 0
begin
    set rowcount 0
    select * from #mytemp where au_id = @au_id
    delete #mytemp where au_id = @au_id

    set rowcount 1
    select @au_id = au_id from #mytemp<BR/>
end
set rowcount 0

A second method is to use the min function to "walk" a table one row at a time. This method catches new rows that were added after the stored procedure begins execution, provided that the new row has a unique identifier greater than the current row that is being processed in the query. For example:

/********** example 2 **********/ 

declare @au_id char( 11 )

select @au_id = min( au_id ) from authors

while @au_id is not null
begin
    select * from authors where au_id = @au_id
    select @au_id = min( au_id ) from authors where au_id > @au_id
end

NOTE: Both example 1 and 2 assume that a unique identifier exists for each row in the source table. In some cases, no unique identifier may exist. If that is the case, you can modify the temp table method to use a newly created key column. For example:

/********** example 3 **********/ 

set rowcount 0
select NULL mykey, * into #mytemp from authors

set rowcount 1
update #mytemp set mykey = 1

while @@rowcount > 0
begin
    set rowcount 0
    select * from #mytemp where mykey = 1
    delete #mytemp where mykey = 1
    set rowcount 1
    update #mytemp set mykey = 1
end
set rowcount 0