Log Shipping – NORECOVERY vs. STANDBY mode

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