Note: This question is part of a series of questions that present the same scenario. Each question in this series
contains a unique solution. Determine whether the solution meets the stated goals.
The Account table was created by using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies
each account. The ProductCode column has 100 different values. The values are evenly distributed in the
table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:
CREATE NONCLUSTERED INDEX IX_Account_ProductCode ON Account(ProductCode);
Does the solution meet the goal?
A.
Yes
B.
No
Explanation:
ExplanationExplanation/Reference:https://msdn.microsoft.com/en-za/library/ms189280.aspx
The answer is ‘NO’.
28
0
Yes it must be “no” because the index created only meets the goal for the first select.
9
2
It does not. Since the sum of the Balance needs to be determined, the query will result in a full table scan since a lookup of 99% of all records is way too expensive. It would have worked for the first query if the condition was = instead of .
So the index only is useful for the second select.
2
0