SQL server temporary tables vs cursors

In SQL Server stored pr开发者_开发百科ocedures when to use temporary tables and when to use cursors. which is the best option performance wise?

If ever possible avoid cursors like the plague. SQL Server is set-based - anything you need to do in an RBAR (row-by-agonizing-row) fashion will be slow, sluggish and goes against the basic principles of how SQL works.

Your question is very vague - based on that information, we cannot really tell what you're trying to do. But the main recommendation remains: whenever possible (and it's possible in the vast majority of cases), use set-based operations - SELECT, UPDATE, INSERT and joins - don't force your procedural thinking onto SQL Server - that's not the best way to go.

So if you can use set-based operations to fill and use your temporary tables, I would prefer that method over cursors every time.

Cursors work row-by-row and are extremely poor performers. They can in almost all cases be replaced by better set-based code (not normally temp tables though)

Temp tables can be fine or bad depending on the data amount and what you are doing with them. They are not generally a replacement for a cursor.

Suggest you read this: http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

I believe SARAVAN originally made the comparison between cursors and temp tables because many times you are confronted with a situation where using a temp table with an identity column and a @counter variable can be used to scroll/navigate through a data set much like one in a cursor.

In my experience, using the temp table (or table variable) scenario can help me get the job done 95% of the time and is faster than the typically slow cursor.