What is an alternative to cursors for sql looping?

Question

Using SQL 2005 / 2008

I have to use a forward cursor, but I don't want to suffer poor performance. Is there a faster way I can loop without using cursors?

Answer 1

There may be some scenarios where one can use Tally tables. It could be a good alternative of loop and cusrors but remember it cannot be applied in every case. A well explain case can be found here

Answer 2

Common Table Expressions would be a good alternative as @Neil suggested. Here's an example from Adventureworks:

WITH cte_PO AS 
(
SELECT [LineTotal]
  ,[ModifiedDate]
FROM [AdventureWorks].[Purchasing].[PurchaseOrderDetail]
),
minmax AS
(
    SELECT MIN([LineTotal]) as DayMin
        ,MAX([LineTotal]) as DayMax
        ,[ModifiedDate]
    FROM cte_PO
    GROUP BY [ModifiedDate]
)
SELECT * FROM minmax ORDER BY ModifiedDate

Here's the top few lines of what it returns:

DayMin     DayMax     ModifiedDate
135.36     8847.30    2001-05-24 00:00:00.000
129.8115   25334.925  2001-06-07 00:00:00.000

Answer 3

Don't use a cursor, instead look for a set-based solution. If you can't find a set-based solution... still don't use a cursor! Post details of what you are trying to achieve, someone will be able to find a set-based solution for you.