Jason Lees-299789 (11/26/2009)
As an aside, one of my colleagues ran some internal tests on a somewhat larger data set (1000 rows, same number of columns) and found that the Cursor ran significantly faster than the last option.
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.
No Arguments there, in fact i pretty much said that martins was the best non-set based solution, and im not saying that a cursor should be considered and that set based operations are not the best way.
To be honest there was only one situation ive come accross that needed while loop, and never one that needed a cursor in stead of Set based theories.
The issue i had with the article was that the sample data set is miniscule, so it doesnt proove that cursors are worse than loops in real world situations.
_________________________________________________________________________SSC Guide to Posting and Best Practices