SQL Server Questions and Answers – Transaction – 2

«
»

This set of SQL Server Multiple Choice Questions & Answers (MCQs) for campus interviews focuses on “Transaction-2”.

1. Which of the following argument is ignored by database engine in COMMIT TRANSACTION?
a) @tran_name_variable
b) @tran_name
c) transaction_name
d) all of the mentioned
View Answer

Answer: a
Explanation: transaction_name specifies a transaction name assigned by a previous BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers

2. Point out the correct statement.
a) ROLLBACK TRANSACTION with a savepoint_name or transaction_name rolls back to the beginning of the transaction
b) A ROLLBACK TRANSACTION statement does produce any messages to the user
c) Issuing a COMMIT TRANSACTION when @@TRANCOUNT is 0 results in an error
d) All of the mentioned
View Answer

Answer: c
Explanation: Error condition implies that there is no corresponding BEGIN TRANSACTION.

advertisement

3. ____________ is the preferred statement for indicating errors in the transaction.
a) SELECT
b) PRINT
c) RAISERROR
d) All of the mentioned
View Answer

Answer: c
Explanation: If warnings are needed in stored procedures or triggers, use RAISERROR.

4. Which of the following query deletes a job candidate?
a)

BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION;

b)

advertisement
BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
GO
ROLLBACK TRANSACTION;

c)

BEGIN TRANSACTION;
GO
DELETE FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
GO
END;

d) All of the mentioned
View Answer

Answer: a
Explanation: You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued because the data modifications have been made a permanent part of the database.

5. Point out the wrong statement.
a) It is the responsibility of the Transact-SQL programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct
b) The Database Engine increments the transaction count within a statement only when the transaction count is 1 at the start of the statement
c) You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued
d) COMMIT transaction requires membership in the public role
View Answer

Answer: b
Explanation: The Database Engine increments the transaction count within a statement only when the transaction count is 1 at the start of the statement.

advertisement

6. A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of __________
a) escalations
b) containment
c) contention
d) none of the mentioned
View Answer

Answer: a
Explanation: These locks are not released, and they are not converted back to their previous lock mode.

7. What is the fundamental difference between COMMIT and COMMIT WORK?
a) COMMIT TRANSACTION accepts a user-defined transaction name
b) COMMIT WORK accepts a user-defined transaction name
c) COMMIT TRANSACTION accepts a user-defined function
d) COMMIT WORK accepts a user-defined stored procedure
View Answer

Answer: a
Explanation: COMMIT syntax, with or without specifying the optional keyword WORK, is compatible with SQL-92.

8. Implicit transactions are maintained by SQL Server for ___________
a) SELECT
b) DDL
c) DML
d) All of the mentioned
View Answer

Answer: d
Explanation: If there is an error occurring within these statements individually, SQL Server will roll back the complete statement.

9. ROLLBACK WORK always rolls back to the outermost BEGIN TRANSACTION statement for ___________ transactions.
a) Simple
b) Nested
c) Distributed
d) None of the mentioned
View Answer

Answer: b
Explanation: When nesting transactions, ROLLBACK WORK decrements the @@TRANCOUNT system function to 0.

10. Which of the following condition will ignore the request to enable delayed durability?
a) DELAYED_DURABILITY = FORCED
b) DELAYED_DURABILITY = 0
c) DELAYED_DURABILITY = IGNORED
d) DELAYED_DURABILITY = NULL
View Answer

Answer: a
Explanation: The request is ignored if the database has been altered with DELAYED_DURABILITY = DISABLED or DELAYED_DURABILITY = FORCED.

advertisement

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server for campus interviews, here is complete set of 1000+ Multiple Choice Questions and Answers.

advertisement
advertisement
advertisement
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He is Linux Kernel Developer & SAN Architect and is passionate about competency developments in these areas. He lives in Bangalore and delivers focused training sessions to IT professionals in Linux Kernel, Linux Debugging, Linux Device Drivers, Linux Networking, Linux Storage, Advanced C Programming, SAN Storage Technologies, SCSI Internals & Storage Protocols such as iSCSI & Fiber Channel. Stay connected with him @ LinkedIn