You are your company’s SQL administrator. A SQL Server 2008 instance named SQL1 contains several databases, including a database named Products.
Several users are experiencing problems with the Product_details table of the Products database. The Product_details table is very large.
You decide to check the integrity of the table using a DBCC CHECKTABLE statement. The statement you execute must meet the following goals:
* The run time for the statement must be minimized.
* Other users should only be able to read the table’s data during the statement’s execution.
Which arguments should you use? (Choose all that apply.)
A.
REPAIR_FAST
B.
TABLOCK
C.
PHYSICAL_ONLY
D.
ESTIMATEONLY
E.
DATA_PURITY
Explanation:
You should use the TABLOCK and PHYSICAL_ONLY arguments with the DBCC CHECKTABLE statement. The TABLOCK argument ensures that other users are only able to read the table’s data during the statement’s execution. If you do not specify this argument, other users will have full access to the table’s data. The PHYSICAL_ONLY argument minimizes the run time for the statement. Using this argument is recommended for large tables. However, you should periodically run the DBCC CHECKTABLE statement without the PHYSICAL_ONLY argument. You should not use the REPAIR_FAST argument with the DBCC CHECKTABLE statement. The REPAIR_FAST argument is only included in SQL Server 2008 for backward compatibility. The repair modes include REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS. When these arguments are used, the database must be in single-user mode, meaningusers would not be able to read the table’s data. You should not use the ESTIMATEONLY argument with the DBCC CHECKTABLE statement. The ESTIMATEONLY argument is used to determine the amount of tempdb space needed to run the statement. You should not specify the DATA_PURITY argument with the DBCC CHECKTABLE statement. The DATA_PURITY argument allows you to check column values to ensure that the data in the columns is valid and within the allowed range.Objective: Maintaining a SQL Server Database Sub-Objective: Maintain database integrity.
References: TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > DBCC (Transact-SQL) > DBCC CHECKTABLE (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and Changing Data > Accessing and Changing Database Data > Locking and Row Versioning > Locking in the Database Engine > Lock Modes