DELETES not wrapped in a transaction, will they commit after each one?

Question

DELETES not wrapped in a transaction, will they commit after each one?

ggoble

Hall of Fame

Points: 3337
More actions
June 18, 2014 at 12:20 pm

#396932

If I run a single query of a bunch DELETES statements and it is not wrapped in a BEGIN TRAN, COMMIT TRAN Will my deletes commit after each statement?
If I stop the statement is it going to rollback all the deletes or just the current one?
BEGIN
DELETE FROM...
DELETE FROM...
DELETE FROM...
DELETE FROM...
DELETE FROM...
DELETE FROM...
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

ggoble (6/18/2014)
If I run a single query of a bunch DELETES statements and it is not wrapped in a BEGIN TRAN, COMMIT TRAN Will my deletes commit after each statement?
If I stop the statement is it going to rollback all the deletes or just the current one?
BEGIN
DELETE FROM...
DELETE FROM...
DELETE FROM...
DELETE FROM...
DELETE FROM...
DELETE FROM...
END

That is NOT single query. It is a whole bunch of queries. The BEGIN END do nothing here at all. If you have this is SSMS and click STOP at some point during execution it will stop wherever in the list it is. That means that any of the DELETE statements that have processed will not be rolled back.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Just to be clear on what Sean said.

The current delete is rolled back and the previous ones remain commited.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ggoble Hall of Fame Points: 3337 More actions · Answer 3

Thank you both! Sorry for poor wording, misuse of terminology, and bad forum etiquette. :blush:

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

ggoble (6/18/2014)
Thank you both! Sorry for poor wording, misuse of terminology, and bad forum etiquette. :blush:

No need to apologize for anything. You asked a very legitimate question in the proper etiquette. If anything I said was misinterpreted to indicate I felt in any way you did anything improper please know that I do not feel that way at all.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101097 More actions · Answer 5

If I run a bunch of DELETE statements not wrapped in a BEGIN TRAN, COMMIT TRAN, will my deletes commit after each statement?

Yes, because SQL DELETE | INSERT | UPDATE statements always run as part of a transaction. If you don't explicitly specify one, SQL creates one for you.

There's at least one complication, though. Certain errors will cause the entire batch to fail, while other errors only cause the statement to fail.

For example:

DELETE FROM A ...

DELETE FROM B ...

DELETE FROM C ...

DELETE FROM D ...

DELETE FROM E ...

Say DELETE on C gets an error. SQL might just rollback the C DELETEs and continue with DELETEing from D and E, or it might cancel all further DELETEs, depending on the specific error that occurred.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".