Corrupted database table with invalid query: what has it done and will it roll back?

Question

I made the error of running what should have been a quick update against my fact table (200M rows) with this:

update dbo.primary_fact
   set count_of_loan_obligors = o.n              
  from dbo.staging_fact f  
       -- notice that this is not the same table as the one in the update clause
       inner join ##Obligor_Count o
       on (f.time_dimension_id = o.time_dimension_id
           and f.account_dimension_id = o.account_dimension_id)

It should have been:

  from dbo.primary_fact f

A correctly formed update like this (1 day, 87k accounts) typically finishes in a minute or 2. After running for 12 minutes, I wondered what was taking so long and spotted my error.

I cancelled the query in SQL Server Management Studio which I understand will roll back all the awful I caused (can someone confirm?)

But my bigger question is: What does the incorrectly formed query do?

---

Update: The cancel action finally completed, an hour and 39 minutes later. DBAs were too slow on the kill -- just as well.

Properly formed update finished in 8 seconds.

Second Update: There were no values set from the original (faulty) update following the successful cancel order in SSMS. I would interpret this to mean that any pending updates were rolled back.

Answer 1

But my bigger question is: What does the incorrectly formed query do?

It will update dbo.primary_fact.count_of_loan_obligors with the same value for all rows. The value will be some value from ##Obligor_Count.n. It is hard to figure out what value that would be.

Here is a little test that basically does what you did:

declare @T1 table (ID int)
declare @T2 table (ID int)

insert into @T1 values (0)
insert into @T1 values (0)

insert into @T2 values (2)
insert into @T2 values (1)

update @T1
set ID = T2.ID
from @T2 as T2

select *
from @T1

Result:

ID
2
2

In this case @T1 is updated with the first row in @T2.