The general advice within the SQL Server community is do not use any T-SQL cursors at any time.
Coding guidelines:
- Avoid using cursors and try solve the problem by using set based operations.
- Try to base them on readonly tables or local (#temptable) temporarily objects.
- Cleanup! Always CLOSE and DEALLOCATE cursors.
- Specify the 'LOCAL' option.
- Specify the FAST_FORWARD clause on DECLARE CURSOR. This opens an optimized forward-only, read-only cursor.
- Never use cursors in OLTP environments.
- Never use them as source of an update: UPDATE table SET col1 = 2 WHERE CURRENT OF the_cursor;
- Cursors are memory intensive; base them on smaller sets (less 5,000-10,000 rows, your site could differ: test!).
template:
DECLARE the_cursor CURSOR FAST_FORWARD LOCAL
FOR
SELECT col1,col2,col3
FROM dbo.mytable
OPEN the_cursor
FETCH
NEXT
FROM the_cursor INTO @col1,@col2,@col3
WHILE
(@@FETCH_STATUS
<>
-1)
BEGIN
FETCH
NEXT
FROM the_cursor INTO @col1,@col2,@col3
END
CLOSE the_cursor
DEALLOCATE the_cursor
TIP:
If you do need cursors in high load OLTP environments because of some complex calculation that can't be done set based take the following approach:
Copy the required result set in a temporary object. Retrieve only the rows and columns you need, but do include all the fields of the primary key.
create #temptable (CalcValue int, pkCol int)
INSERT INTO #temptable (CalcValue, pkCol)
SELECT 0, PrimaryKeyCol
FROM dbo.HighLoadOLTPtable
WHERE -- your where clause here
Base your cursor on the temp. object.
Loop the cursor, perform your calculation and store the result in the temp. object row:
UPDATE #temptable SET CalcValue=complex_calculated_value
WHERE pkCol=pk_fields_as_fetched_by_cursor
When done looping close/deallocate the cursor, and update the high load OLTP source table(s) set based by primarykey, use a BEGIN TRANSACTION / COMMIT if required:
UPDATE dbo.HighLoadOLTPtable SET CalculatedValue = #temptable.CalcValueFROM dbo.HighLoadOLTPtable, #temptableWHERE dbo.HighLoadOLTPtable.PrimaryKeyCol = #temptable.pkCol
