Updating row selected by cursor sermons on dating for christians
SQL is a set based language--that's what it does best.
Then, as you cycle through the cursor, you are making changes to other tables and still keeping all of the memory and locks of the cursor open.
Each one has different performance characteristics and associated overhead.
Make sure you use the correct cursor type for your operation. One argument for using a cursor is when you need to process and update individual rows, especially for a dataset that doesn't have a good unique key.
Consider a combination of metadata and dynamic-sql. Or iterate through this resultset and use dynamic-SQL (sp_executesql) to perform the operations?
As a very simple example, say I have 100 records in a table that define the names of tables that I want to copy/truncate/whatever. There is no way to achieve the above objective using set-based SQL.
So, to use cursors or a while loop (pseudo-cursors)?