Transaction

Question

Transaction

sunny.tjk

SSCertifiable

Points: 5041
More actions
February 8, 2012 at 10:40 am

#267973

Could someone please tell me if the following query is correct:
Begin Transaction
Delete from tableabcd where starttime < '1/1/2011'
Rollback Transaction

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

sunny.tjk (2/8/2012)
Could someone please tell me if the following query is correct:
Begin Transaction
Delete from tableabcd where starttime < '1/1/2011'
Rollback Transaction

Well that depends on what you define as "correct". There do not appear to be any syntax errors.

_______________________________________________________________

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/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

The syntax is correct, but of course it does nothing because you are rollbacking the transaction. If what you intended to do is to delete some rows and then rollback the transaction so the rows will be returned to the table, then it should work.

I also recommend that you’ll start using the ISO standard for dates (style number 112 when you use convert function). I admit that with the date that you wrote, you shouldn’t have problems but with other dates you can run into problems. For example – take the date 08/02/2012. In some cases it will be considered as February 8 and in other cases it will be considered as August 2th.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 3

What I'd like to do is delete rows older than 7/1/2011. If an error occurs, I'd like to roll back.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Delete from tableabcd where starttime <= 2011/07/01

That's all you need. Deletes are atomic, they complete or rollback as a single operation. A delete can't partially succeed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 5

GilaMonster (2/8/2012)
Delete from tableabcd where starttime <= 2011/07/01
That's all you need. Deletes are atomic, they complete or rollback as a single operation. A delete can't partially succeed.

hi gail,

is that same for update also?

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 7

GilaMonster (2/8/2012)
Yes.

Thanks.

Then these begin and rollback transactions are used for whcih TSQL statments??

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

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

SKYBVI (2/8/2012)
GilaMonster (2/8/2012)
Yes.
Thanks.
Then these begin and rollback transactions are used for whcih TSQL statments??
Regards,
Skybvi

If you have a batch of updates for example. Maybe you need to update 3 different tables, but if there is an error you don't want to update anything. Put that all in a transaction and you can rollback the whole batch.

begin try

begin transaction

update table1 set col = 'val'

update table2 set col = 'val'

update table3 set DateTimeColumn = 'asdf'

commit transaction

end try

begin catch

rollback transaction

end catch

In the above example if DateTimeColumn is a datatype of datetime it would fail. The catch block would then rollback the entire transaction and not just the last update.

_______________________________________________________________

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

For when you want multiple statements to commit or rollback as atomic operations

Please read basics of transactions (including ACID) in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 10

Thanks Sean and Gail.

Just 1 question more,

Would the case be different if I am updating 2 tables which have triggers attached to them whcih fire upon updating the particular columns of the table?

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

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

Anything in your trigger is also part of the transaction so it will be commited/rollback as well.

The best thing for you on questions like these is to throw some simple tables together and test it out. Of course we can answer but I guarantee it will sink in deeper if you explore it.

_______________________________________________________________

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/