nerdexam
Microsoft

70-463 · Question #2

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] -

The correct answer is C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].. This question tests the ability to design a minimal columnstore index that satisfies all query patterns by including only the columns actually referenced across the queries.

Design and implement a data warehouse

Question

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 a columnstore 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 meets the requirements. The strategy must also minimize the number and size of the indexes. What should you do?

Options

  • ACreate one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and
  • BCreate three coiumnstore indexes:
  • CCreate one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
  • DCreate two columnstore indexes:

How the community answered

(20 responses)
  • A
    5% (1)
  • B
    10% (2)
  • C
    60% (12)
  • D
    25% (5)

Why each option

This question tests the ability to design a minimal columnstore index that satisfies all query patterns by including only the columns actually referenced across the queries.

ACreate one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and

Including ID in the columnstore index adds unnecessary width since no query references that column, violating the requirement to minimize index size.

BCreate three coiumnstore indexes:

Creating three separate columnstore indexes adds redundant storage for QuoteDate and overlapping columns, violating the requirement to minimize the number of indexes.

CCreate one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].Correct

All three queries filter on QuoteDate and aggregate on Close, High, Low, or Volume, so a single columnstore index containing exactly those five columns covers all queries. Excluding ID, which no query references, minimizes index size while a single index minimizes the index count.

DCreate two columnstore indexes:

Creating two indexes stores overlapping columns (QuoteDate is duplicated) when a single index can satisfy all three queries, failing to minimize the number of indexes.

Concept tested: Columnstore index column selection for analytic queries

Source: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance

Topics

#columnstore index#fact table#indexing strategy#query optimization

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-463 Practice