In this SQL Tutorial I will show you how you can track the progress of SQL Update. Today I had to do one off update of a table with 5 million rows with relatively complex SQL and I like to see progress of long running queries so below is code that I used to check SQL Update progress status.
I have used SQL Server 2008 R2 in my example but it should work in 2005 and 2012 (I think)
First example of an update:
SET NewField = NewValue
Below is my t-sql code; the trick is to do Count(*) with WHERE IS NULL and the most crucial part is with (nolock) (dirty read)
FROM [FactWarehouse].[dbo].[Contacts] with (nolock)
where [DWH_AssociateDirectorate] is null
The result of the above t-sql query shows me rows that hasn't been updated yet. The with (nolock) is a dirty read which means that the update might be rolledback (undo) if for instance I click cancel button on my update statement but in my case I take advantage of that to check progress status of my long running update.
You might have to update WHERE in second example to match your first update critiera so you can see rows that haven't been updated yet but meet update critieria.
I hope that will help you with your SQL Update progress tracking