-->

Ads 720 x 90

How to use SQL Server T-SQL cursors

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

Related Posts

There is no other posts in this category.

Total Pageviews

Subscribe Our Newsletter