26

In SQL Server stored procedures when to use temporary tables and when to use cursors. which is the best option performance wise?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
SARAVAN
  • 14,571
  • 16
  • 49
  • 70
  • Neither if you can help it. SQL is SET based, and can generally handle the situations most use temp tables and/or cursors for. – OMG Ponies Dec 31 '10 at 06:15
  • 1
    Why not CTE? http://msdn.microsoft.com/en-us/library/ms175972.aspx – Adrian Godong Dec 31 '10 at 06:15
  • 1
    Can you clarify a bit here? Using temporary tables vs using cursors is a bit like apples and oranges. Temporary tables vs table variables would be a more appropriate comparison. – tcnolan Dec 31 '10 at 06:17
  • 1
    If I have a stored procedure which runs a bunch of set based operations, and I want to run it for say 100 different companies in my database. I think it's ok to either cursor over the 100, or use a temp table to iterate through the 100. Is there any difference between the two in this situation? I had though cursors used a table variable behind the scenes so it wouldn't make much difference. Does it? – Jens Frandsen Sep 02 '16 at 19:14

3 Answers3

26

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
9

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

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

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.

Cc Rr
  • 11
  • 1