jarred.nicholls (11/26/2009)

---

Cursors and temp tables are very similar, since a cursor creates a temp table in tempdb (spooling all data to disk before being read back)...just like a temp table does. A cursor has an upfront hit when allocating itself, but that overhead doesn't occur with each row fetch and each row fetch is quite speedy.

The CPU cycles is much greater on each row's operation in the identity temp table method (it is doing a COUNT(*) on the temp table each iteration). With a large data set, a cursor will (always) startup slower but finish faster and with less CPU. On a small data set, a cursor will still startup slower but finish slower as well.

Bottom line is, the last method makes certain assumptions about the identity column that can't always be made and thus isn't a universal solution. And of course, 99% of the time there is a set-based solution that will run circles around any procedural solution. I once tuned a stored procedure that took over 2 hours to complete by replacing a cursor with a set-based solution and brought the execution time down to just around 15 seconds. If you are forced into a procedural situation, first post your situation onto a forum and let someone find a set-based solution to the problem (there's a great chance there is one), and if none is found, use a method that suits the size of the data set. Martin's solution is the appropriate one here, no matter the size of the data set - it doesn't use a temp table.