SQL server temporary tables vs cursors

sql stored-procedures temp-tables
SARAVAN picture SARAVAN · Dec 31, 2010 · Viewed 31.1k times · Source

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

Answer

marc_s picture marc_s · Dec 31, 2010

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.

Related questions

SQL Performance Slow (Improve Insert Into Temp Table)

I have been working on an old auditing stored procedure that is running slowly, I have had some success from applying indexing and making the query more sargable. However the stored procedure is still taking over one minute to complete. …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More