Exhibit:
Domain.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 Certkiller 1
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 2
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 3
ADD OptOutFlag BIT NULL
IF @@error <> 0ROLLBACK TRANELSECOMMIT TRAN
B.
BEGIN TRAN
BEGIN TRY
ALTER TABLE Certkiller 1
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 2
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 3
ADD OptOutFlag BIT NULL
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN
END CATCH
COMMIT TRAN
C.
ALTER TABLE Certkiller 1
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 2
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 3
ADD OptOutFlag BIT NULL
D.
BEGIN TRAN
ALTER TABLE Certkiller 1
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 2
ADD OptOutFlag BIT NULL
ALTER TABLE Certkiller 3
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.