Кто знает почему ответ D а не E?

QUESTION NO: 33
You are a database developer for an insurance company. You are tuning the performance of queries in
SQL Query Analyzer. In the query pane, you create the following query:
SELECT P.PolicyNumber, P.IssueState, AP.Agent
FROM Policy AS P JOIN AgentPolicy AS AP
ON (P.PolicyNumber = AP.PolicyNumber)
WHERE IssueState = ‘IL’
AND PolicyDate BETWEEN ‘1/1/2000’ AND ‘3/1/2000’
AND FaceAmount > 1000000
You choose “Display estimated execution plan” from the query menu and execute the query. The query
execution plan that is generated is shown in the estimated execution plan exhibit.
...
What should you do?
A. Rewrite the query to eliminate BETWEEN keyword.
B. Add a join hint that includes the HASH option to the query.
C. Add the WITH (INDEX(0)) table hint to the Policy table.
D. Update statistics on the Policy table.
E. Execute the DBCC DBREINDEX statement on the Policy table.

Answer: D.
Explanation: Updating the statistics in the Policy table could make the Query optimizer use the indexes in a
better way. This would increase performance.
Note 1: Unexpectedly long-lasting queries and updates can be caused by:
• Slow network communication.
• Inadequate memory in the server computer.
• Lack of useful statistics.
• Out-of-date statistics.
• Lack of useful indexes.
• Lack of useful data striping.
Note 2: An index scan is used. An index seek would be faster.
Reference:
BOL, Query Tuning
INF: Troubleshooting Slow-Running Queries on SQL Server 7.0 or Later (Q243589)
BOL, Understanding Hash Joins
BOL, Table hints
Incorrect answers:
A: The syntax of the BETWEEN clause is correct and will return values that are true for the test expression,
i.e. policy dates that are greater than or equal to 1/1/2000 and less than or equal to 3/1/2000. There is no
way to rewrite the query, keep the result set, and improve the performance.
Note: The BETWEEN operator specifies a range to test. The result of this test is returned as a Boolean
value, i.e. TRUE, FALSE or UNKNOWN. The value returned id TRUE if the test expression is greater
than or equal to the first expression and less than or equal second expression, and UNKNOWN if any
input to the BETWEEN predicate is NULL. The test expression, the first expression and the second
expression must all be of the same data type. The correct syntax for the BETWEEN operator is:
<test expression> BETWEEN <first expression> AND <second expression>
B: The exhibit indicates that a nested loop is used to join the tables, and that a clustered index scan is on the
AgentPolicy table. A hash join could improve the performance of the join and eliminate the clustered
index scan. The query could run faster.
C: The estimated execution plan shows that a clustered index scan is going to be used, not a seek. It would
be better if the query used an index seek instead. We could try to force an index seek with a table hint.
However, we should use the hint index(1), not the hint index(0).
Index(0): If a clustered index exists, INDEX(0) forces a clustered index scan. But the query is already
using a clustered index scan, so this would be no improvement.
Index(1): If a clustered index exists, INDEX(1) forces a clustered index scan or seek. This could
improve the performance, but it is not listed as an option.
E: Rebuilding an index could improve performance of a query that uses that index. This is particularly true
if the index is fragmented.
Note: DBCC DBREINDEX rebuilds all indexes defined for a table dynamically. In so doing, indexes
enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and recreate
those constraints. This means an index can be rebuilt without knowing the table’s structure or
constraints, which could occur after a bulk copy of data into the table. DBCC DBREINDEX can also
rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP
INDEX and CREATE INDEX statements. Because the work is done by one statement, DBCC
DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX
statements would have to be put in a transaction to be atomic. Furthermore, DBCC DBREINDEX has a
greater level of optimization than can be achieved with individual DROP INDEX and CREATE INDEX
statements.