DP-800: Developing AI-Enabled Database Solutions

By Microsoft · Question Mode

QuestionQ84

VerifiedSecure, optimize, and deploy database solutions

You have an Azure SQL database that stores sales data and includes tables named Sales and Products. Sales has three columns: SalesDate, ProductKey, and TotalSale.

Sales is 10 TB and receives a nightly batch load. Most reporting queries scan large sections of Sales, filter by SalesDate or ProductKey, and aggregate TotalSale by using SUM().

Products is comparatively small and is used mainly for point lookups and joins to Sales.

You need to recommend indexes that optimize the reporting queries while minimizing storage requirements.

Which index type should you recommend for each table? Each index type may be used once, more than once, or not at all.

Drag & Drop
Sales:
Products:
Explanation

Clustered columnstore indexes are the preferred storage for large data-warehouse fact tables and analytics workloads that scan and aggregate large volumes of data; column compression also greatly reduces storage. Rowstore indexes perform best for seeks on particular values or small ranges, making a nonclustered rowstore index suitable for the small Products table’s point lookups and joins.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!