You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named
Quotes. The table has no indexes and consists of seven columns:
[ID]
[QuoteDate]
[Open]
[Close]
[High]
[Low]
[Volume]
Each of the following queries must be able to use acolumnstore index:
SELECT AVG ([Close]) AS [AverageClose]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
SELECT AVG([High] – [Low]) AS [AverageRange]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
SELECT SUM([Volume]) AS [SumVolume]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’
You need to ensure that the indexing strategy meetsthe requirements. The strategy must also minimize the
number and size of the indexes. What should you do?
A.
Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
B.
Create three coiumnstore indexes:
One containing [QuoteDate] and [Close]
One containing [QuoteDate], [High], and [Low]
One containing [QuoteDate] and [Volume]
C.
Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
D.
Create two columnstore indexes:
One containing [ID], [QuoteDate], [Volume], and [Close]
One containing [ID], [QuoteDate], [High], and [Low]
Explanation:
According to these references, this answer looks correct.
Reference: http://msdn.microsoft.com/en-us/library/gg492088.aspx
Reference: http://msdn.microsoft.com/en-us/library/gg492153.aspx