Does stopping query with a rollback guarantee a rollback
sql, sql-server
Solution
A single update will not update some rows. It will either update all or 0.
So, if you cancel the query, nothing will be updated.
This is atomicity database systems which SQL Server follows.
In other words, you don't have to do that rollback at the end, nothing was committed anyway.
When you cancel a query, it will still hold locks until everything is rolled back so no need to panic.
You could test it yourself, execute the long query, cancel it and you will notice that it takes a while before the process really end.
Problem
Say I have a query like this: ``` BEGIN Transaction UPDATE Person SET Field=1 Rollback ``` There are one hundred million people. I stopped the query after twenty minutes. Will SQL Server rollback the records updated?