Your data warehouse 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 following table. Table nameStores customer surnamebeginning with letterCustomer1A C HCustomer2I C PCustomer3Q C Z 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 CUSTOMER1
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER2
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER3
ADD OptOutFlag BIT NULL
IF @@error <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
B.
BEGIN TRAN
BEGIN TRY
ALTER TABLE CUSTOMER1
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER2
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER3
ADD OptOutFlag BIT NULL
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN
END CATCH
COMMIT TRAN
C.
ALTER TABLE CUSTOMER1
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER2
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER3
ADD OptOutFlag BIT NULL
D.
BEGIN TRAN
ALTER TABLE CUSTOMER1
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER2
ADD OptOutFlag BIT NULL
ALTER TABLE CUSTOMER3
ADD OptOutFlag BIT NULL
COMMIT TRAN