You have a Test database that resides on an instance of SQL Server. The Test database contains the Item table, which includes the ItemNo and Description columns as follows:
Correct Answer: C
Explanation/Reference:
In this scenario, you executed Transact-SQL code that uses transactional savepoints. Savepoints allow you to roll back portions of transactions as needed. To create a savepoint to which you can roll back, you use the SAVE TRANSACTION statement and specify a savepoint name. Then, when you issue a ROLLBACK statement, you can specify the savepoint to which you want to roll back. In this scenario, you issued twoINSERT statements to insert the first two rows and a DELETE statement that deletes the first inserted row. Then, you issued a SAVE TRANSACTION statement to create a savepoint named SaveA. Next, you inserted rows 3 and 4, and created another savepoint named SaveB. Having multiple savepoints allows you to roll back to different places within the same transaction. Next, you inserted another row in the table, and updated the row with ItemNo 5. Then, you issued a ROLLBACK TRANSACTION statement specifying SaveA. This statement rolled back all the changes that had occurred since creating the SaveA savepoint.
After the rollback, you issued an update to ItemNo 4. However, the INSERT for this row was previously rolled back. Therefore, no update occurred.
Therefore, after you commit the transaction, only the second row inserted into the table will remain as shown:
You should note that when rolling back to a savepoint, SQL Server holds resources until the entire transaction is either committed or rolled back. Therefore, you should always issue a COMMIT or a complete ROLLBACK even if you have previously rolled back part of the transaction.
