SQL Server - resume cursor from last updated row -

I want to make cursor updates (rows of millions) of the table. If it starts again, then the script should resume from the last updated line (for example, if the server restarts).

What is the best way to resolve it? Create a new table with last saved id? Use the extended assets of tables to save this information?

I get "update date" or "last process date" or something similar in the named datetime column in your table Use it. When your update is in progress, just process any number of records that are not the maximum updates or tap:

  where UpdateDate & lt; (Select Max from MyTable) or UpdateDate is empty  

This is probably a good idea to catch the maximum update ( @maxUpdateDate ?) At the beginning Your process / loop does not change during a batch, and in the same way to update every line you go to get a new update date ( @ newUpdateDate ?) At the beginning of your process . A UTC date will work best to avoid changes in DTT timing.

This data will now be a real feature of your unit, not metadata or temporary placeholder, and this appears to be the best way to appear consistent and otherwise completely more self- Documentary will also be done, and indexes can be indexed when the need arises. A date can also keep important temporary information about your data, while IDs and flags do not.

By doing this in such a way, other tables or expanded properties will have unnecessary data collection.

Some other ideas:

• Do not use any floating table which may disappear in many scenarios where you have all the rows (connection loss, server restart, etc.) Not acted on.
• Do not use any identity or other ID which can fill the gap, be researched, cut back in 0, etc.
• The idea of ​​having the maximum value stored in another table (essentially rolling its own sequence object) is generally learned and in terms of what I have read, a suspicious practice in SQL Server It is shown, though I have a difficult problem finding a good article.
• If possible, avoid the cursor in favor of batches and generally avoid batches in favor of full set-based updates.
• sp_updateextendedproperty seems to behave correctly with rollback, though, I'm not sure how locking works with it - just FYI if you finally go down that path Decide.