Alternatives to sql cursor

Question

what are the alternatives to using cursors in sql server. i already know a trick which involves using the Row_Number() function which numbers the rows then i can loop over them one by one. any other ideas?

Answer 1

When I don't want to complicate things with SQL cursors I often populate temporary tables or table variables, then do a while loop to go through them.

For example:

declare @someresults table (
    id int,
    somevalue varchar(10)
)

insert into @someresults
   select 
       id,
       somevalue
   from
       whatevertable

declare @currentid int
declare @currentvalue varchar(10)

while exists(select 1 from @someresults)
begin
    select top 1 @currentid = id, @currentvalue = somevalue from @someresults

    --work with those values here

    delete from @someresults where id = @currentid
end

Answer 2

Several options:

1. Best is to re-analyze the problem from a Mathematical Set-based perspective. If this can be done, it will most likely provide the best solution in both calrity and performance.
2. Second, use a Temporary table variable to store only the keys. Insert the keys into this temp table variable using a recursive Common table expression if possible, or failing that, use a T-SQL programming loop (Where Clause or constructed iterative loop of some kind), and then when the temp table variable has all the key values in it, use it to join to the real tables in the appropriate way to execute whatever your real SQL design goal happens to be... Use only the keys as you recursively or iteratively build the temp table to keep it as narrow as possible during the expensive construction phase...
3. use a temporary table (on disk) in a similar way to the above. This is a better choice when you need this temp table variable to contain more than a few columns and/or a very large (> 1M) number of rows, or if you need the temp table to have more than a primary Key index....