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_Staff1, CK_Staff 2 and CK_Staff 3. 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
Stores employee surnames
beginning in letters
CK_Staff1 A – J
CK_Staff2 K – P
CK_Staff3 R – Z
The database is configured in order that Domain.com employees are unable to opt-out of e-mail notifications to their personal email addresses. You have received instruction from the CIO to add a new OptOutFlag column to every CK_Staff table.
You need to ensure the following:
1. The column is added to either all three CK_Staff 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_Staff1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff3
ADD OptOutFlag BIT NULL
COMMIT TRAN
B.
ALTER TABLE CK_Staff1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff3
ADD OptOutFlag BIT NULL
C.
BEGIN TRAN
ALTER TABLE CK_Staff1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff3
ADD OptOutFlag BIT NULL
IF @@error <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
D.
BEGIN TRAN
BEGIN TRY
ALTER TABLE CK_Staff1
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff2
ADD OptOutFlag BIT NULL
ALTER TABLE CK_Staff3
ADD OptOutFlag BIT NULL
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN
END CATCH
COMMIT TRAN