Exhibit:
TestKing.com is storing its customer data in a data warehouse.It includes three separate SQL Server 2005 tables for storing customer data. Each table is used to horizontally partition data that is migrated from a SQL Server 2000 installation to SQL Server 2005.
Data is partitioned as shown in the table displayed in the exhibit.
The database does not allow customers to opt-out of e-mail marketing offers. You need to add a new OptOutFlag column to each of the customer tables. You must ensure that this column is added to either all three customer tables or none of them. You must also ensure that the database is always in a consistent state. Which Transact-SQL batch should you use?
A.
BEGIN TRAN ALTER TABLE TESTKING1 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING2 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING3 ADD OptOutFlag BIT NULL IF @@error <> 0ROLLBACK TRANELSECOMMIT TRAN
B.
BEGIN TRAN BEGIN TRY ALTER TABLE TESTKING1 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING2 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING3 ADD OptOutFlag BIT NULL END TRY BEGIN CATCH ROLLBACK TRAN
RETURN END CATCH COMMIT TRAN
C.
ALTER TABLE TESTKING1 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING2 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING3 ADD OptOutFlag BIT NULL
D.
BEGIN TRAN ALTER TABLE TESTKING1 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING2 ADD OptOutFlag BIT NULL ALTER TABLE TESTKING3 ADD OptOutFlag BIT NULL COMMIT TRAN
Explanation:
You should do this using the BEGIN CATCH. If there is an error reported in any of the tables when the OptOutFlag column is inserted, the whole transaction will rollback.