Answer:-
Note:
Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, allindividual statements are committed if they complete successfully.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE (DDL)
FETCH -
REVOKE -
BEGIN TRANSACTION -
GRANT -
SELECT -
CREATE (DDL)
INSERT -
TRUNCATE TABLE -
DELETE (DML)
OPEN -
UPDATE (DML)
DROP (DDL)
Note 2: XACT_STATE returns the following values.
1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction. The transaction is committable.
-1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.the transaction is uncommittable and should be rolled back.
0 There is no active user transaction for the current request. Acommit or rollback operation would generate an error.
Reference:-https://technet.microsoft.com/en-us/library/ms187807(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms189797(v=sql.110).aspx