Cursor and updating same records inside cursor
The cursor name can be referenced in any stored procedure or batch executed by the connection.The cursor is only implicitly deallocated at disconnect.
The developer was “hoping” that it would finish soon given another day or two.Keep in mind SQL Server, as a modern RDBMS system, performs much better with set operations.Here is simplified version of a real cursor that was used to update a big table with over 200 million rows.If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.Specifies that the scope of the cursor is global to the connection.Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates.
DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.
An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates.
The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter.
I need to point out that the non-cursor batch uses more resources since the UPDATE now spawns multiple threads to process parallely.
Remember our goal here is to make this finish faster not worrying about how much resources it consumes.
Between the statement, I also put the database into simple recovery mode and added appropriate indexes to the temp table.