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
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.