You work as a database administrator at Domain.com. Domain.com stores client information in a data warehouse. The data warehouse contains three separate SQL Server 2005 tables for storing client information.
The information stored in three separate tables named CK_DATA1, CK_DATA2 and CK_DATA3. The tables are used to horizontally partition information that is migrated from a SQL Server 2000 installation to a SQL Server 2005. The information is partitioned as shown in the table displayed below:- Table name
Stores customer surname
beginning with letter
CK_DATA1 A – H
CK_DATA2 I – P
CK_DATA3 Q – Z
The database is configured in order that clients are unable to opt-out of e-mail marketing offers. You have received instruction from the CIO to add a new OptOutFlag column to every client tables. You need to ensure the following:-
1. The column is added to either all three client tables or none of them.
2. The database is always in a consistent state.
Which Transact-SQL batch should you use?
A.
BEGIN TRAN
ALTER TABLE CK_DATA1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA3
ADD OptOutFlag BIT NULL
COMMIT TRAN
B.
ALTER TABLE CK_DATA1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA3
ADD OptOutFlag BIT NULL
C.
BEGIN TRAN
ALTER TABLE CK_DATA1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA3
ADD OptOutFlag BIT NULL
IF @@error <> 0ROLLBACK TRAN
ELSE
COMMIT TRAN
D.
BEGIN TRAN
BEGIN TRY
ALTER TABLE CK_DATA1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_DATA3
ADD OptOutFlag BIT NULL
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN
END CATCH
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 will result that the whole transaction will rollback.