The Bit Corner, Inc.

When is A Delete an Insert?

“Hmmm,” said Sam. There’s only one process in your accounts database, and it’s not blocked. But there is something interesting here- the command shows up as “INSERT”, not “DELETE”.

“Could be a bug in sp_who2,” suggested Nully.

“Maybe,” said Sam, “but I doubt it. I think there’s more going on here than meets the eye. Is it okay if we stop this delete, Chuck?”

“Sure, Sam. We’re not in production now. I just want to get to the bottom of this.”

SQL Sam killed the process and then waited for the DELETE operation to rollback. It took a long time because of the large number of records involved.

“Okay, it’s time to look at the query plan,” said Sam. Sam brought up a query window and entered the following:

set rowcount 1
DELETE FROM customers WHERE cust_num LIKE '%8009______'

“What’s the ‘set rowcount 1’ for?” asked Chuck.

“That’s so we can delete just one row and see what happens,” said Sam. He then turned on the Show Query Plan option in the Query Options dialog and ran the query. This is what he saw:

STEP 1
The type of query is DELETE
The update mode is deferred
FROM TABLE
customers
Nested iteration
Table Scan
TO TABLE
customers
STEP 1
The type of query is COND
STEP 1
The type of query is SELECT
FROM TABLE
dbo.customers DELETED
Nested iteration
Using Dynamic Index
STEP 1
The type of query is COND
STEP 1
The type of query is INSERT
The update mode is deferred
FROM TABLE
dbo.customers deleted
Nested iteration
Using Dynamic Index
NOT EXISTS : nested iteration
FROM TABLE
Updated_Customers
Nested iteration
Table Scan
TO TABLE
Updated_Customers

“Okay,” said Sam. “I can now see that your simple DELETE isn’t so simple. I can see several reasons why this delete runs so long.”