Posted on 2014-08-21
Last Modified: 2015-04-21
Hi Experts,
What is the difference between using BEGIN TRANSACTION and END TRANSACTION, and using BEGIN and END?
When do we use one over the other?

Thanks in advance,
Question by:mainrotor
    LVL 25

    Accepted Solution

    Begin and End delimit a block of SQL statements, for example:

    if @myval = 1
     update table...
     insert into ...

    Open in new window

    See here for more:

    If you didn't have the begin and end there it would only do the first statement as part of the IF statement and the insert would always happen.

    Begin transaction allow you to wrap up a series of statements that are held in a transaction so that you can test for error conditions and if one is found you can rollback a transaction. If not you can commit it and it will physically make the changes in the data.

    There isn't an "end transaction" because you either commit or rollback
    LVL 2

    Assisted Solution

    by:Priya Sudharsan
    You need transations when you want to do more than one things, either all of them or none.

    Begin and End are used to group the statements as logical. They may used in IF WHILE and CASE statements.

    You can simply use BEGIN and END without any of the above statements.

    Its just for a readability. They just group the statements and you would be able to see the steps clearly. Thats all.
    LVL 44

    Assisted Solution

    by:Vitor Montalvão
    BEGIN and END are used to define a block of SQL statements depending of the logical of your code (Condition block, Loop block, Stored Procedure block, ...).

    BEGIN TRANSACTION and END TRANSACTION are used to define a block of operations (INSERT, DELETE, UPDATE, SELECT, ...) to act as one and being committed (if everything's ok) or rollback (if something goes wrongs) so you can maintain the integrity of your database.

    Author Comment

    I will try some of your suggestion and get back to you all. Thanks
    LVL 65

    Expert Comment

    by:Jim Horn
    mainrotor - How's it going?  Looks like you asked us a softball question that multiple experts have answered..

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now