Can it be better to create a cursor over data copied to a table variable instead of the actual table?

I've had some bad experiences with cursors causing deadlocks. I have been trying to eliminate cursors wherever possible. When they are unavoidable, I try minimize the amount of work done while the cursor is open.

Is it ever better to copy the data to a table variable, and build a cursor over the开发者_如何学C table variable?

I know the answer will likely depend on the size of the data set. At them moment, I am concerned with a very small, data set (a handlefull of records), that is rarely updated. On occasion I will still have create complex loops over large data sets.

Is there any benefit to copying to table variables, or am I over-complicating things?

If you define the cursor as STATIC when you declare it, SQL Server will make a temporary copy of the data for you automatically.

From the documentation:

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.