HOTSPOT
You are maintaining statistics for a database table named tblTransaction. The table contains more than 10
million records.
You need to create a stored procedure that meets the following requirements:
– On weekdays, update statistics for a sample of the total number of records in the table.
– On weekends, update statistics by sampling all rows in the table.
A maintenance task will call this stored procedure daily.
How should you complete the stored procedure? To answer, select the appropriate Transact-SQL segments in
the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer: 
Explanation:
Box 1: UPDATE STATISTICS
Box 2: SAMPLE 20 PERCENT
UPDATE STATISTICS tablenameSAMPLE number { PERCENT | ROWS }
Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to
use when it updates statistics. For PERCENT, number can be from 0 through 100 and for ROWS, number can
be from0 to the total number of rows.
Box 3: UPDATE STATISTICS
Box 4: WITH FULLSCAN
FULLSCAN computes statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option.
https://msdn.microsoft.com/en-us/library/ms187348.aspx
correct
22
0
For box 2, what makes option 3 better than option 4? Both use “a sample”.
1
1
I guess because the table has over 10 million rows, a sample of 200,000 rows, which accounts for 2% of the table contents, is not significant enough for a daily statistics update.
2
0