Friday, August 6, 2010

Simple Questions on Transactions

A transaction is a group of statements that are combined into a single logical unit. All statements succeed as a whole, or if any one of the statement fails, they all fail as a whole.

BEGIN TRAN --> Marks the starting point of a transaction.
SAVE TRAN Save_point --> Sets a new save point with in a transaction.
COMMIT TRAN --> Marks the end of a transaction and makes the changes permanent.
ROLLBACK [TRAN] [Save_point] --> Rolls back a transaction to the starting point or to the specific save point.



1. The following script if executed, what will be the output if you have only 200 Rupees?

IF Cash > 200
   'Buy Fruits'
   'Buy Snacks'

(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.


2. The following script if executed, what will be the output you have only 200 Rupees?

IF Cash > 200
    BEGIN
        'Buy Fruits'
        'Buy Snacks'
    END

(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.


3. Which one of the following function returns the open transaction count?

(a) @@ERROR
(b) @@FETCH_STATUS
(c) @@TRANCOUNT
(d) @@ROWCOUNT


4. Consider using nested transactions, if any one transaction rollbacks what will happen to other transactions with in the nesting?

(a) All other transactions will be committed except the one which rollback.
(b) All the transactions will be rollback regardless of the nesting level.
(c) None of the transactions will execute.
(d) Creates open transactions.


5. An open transaction prevents others from viewing the data that is modified, which command can be used to make the modifications permanent?

(a) UPDATE TRAN
(b) DELETE TRAN
(c) SELECT TRAN
(d) COMMIT TRAN


6. When using a TRY…CATCH block, which function helps to find out the description of the error?

(a) ERROR_SEVERITY
(b) ERROR_STATE
(c) ERROR_MESSAGE
(d) ERROR_NUMBER


Show Answers:

2 comments:

  1. REAL WORLD EXAMPLE:

    From: "Archana"
    Subject: try-catch and @@error
    Date: Monday, July 12, 2010 5:54 PM

    hello,

    i am having one query in sql server 2008 try catch block.
    if i am using try-catch, then what is use of using @@error in try
    block if any exception i can catch in catch block.

    any help will be truely appreciated.

    thanks in advance.

    -------------------------------------

    From: "Eric Isaacs"
    Subject: Re: try-catch and @@error
    Date: Monday, July 12, 2010 10:44 PM

    @@Error is the old way to handle errors in SQL Server. With @@Error
    you would have to capture that value into another local variable after
    each operation and then check that value to see if it was non-zero.
    With a Try-Catch block, you have more control and no longer need
    @@Error. Instead, you should use ERROR_NUMBER().

    From MSDN:
    In the scope of a CATCH block, the following system functions can be
    used to obtain information about the error that caused the CATCH block
    to be executed:

    * ERROR_NUMBER() returns the number of the error.
    * ERROR_SEVERITY() returns the severity.
    * ERROR_STATE() returns the error state number.
    * ERROR_PROCEDURE() returns the name of the stored procedure
    or trigger where the error occurred.
    * ERROR_LINE() returns the line number inside the routine
    that caused the error.
    * ERROR_MESSAGE() returns the complete text of the error
    message. The text includes the values supplied for any substitutable
    parameters, such as lengths, object names, or times.

    Source: http://msdn.microsoft.com/en-us/library/ms175976.aspx

    -Eric Isaacs

    ReplyDelete