Exam DP-203 topic 4 question 2 discussion

correct!

correct

B. Clustered Columnstore Index Since your Azure Synapse Analytics dedicated SQL pool contains a large fact table with 5 billion rows stored as a heap, and queries typically aggregate values from 100 million rows, the best indexing strategy is a Clustered Columnstore Index (CCI). Columnstore indexes are optimized for large tables and analytical workloads (OLAP). Clustered Columnstore Index (CCI) compresses the data and stores it in columnar format, which significantly improves query performance on aggregations. CCI provides the fastest query times for scenarios where: A large number of rows is scanned. Queries perform aggregations and filtering. The query returns only a few columns from the large dataset

CORRECTO

clustered columnstore index

Clustered index * Tables with up to 100 million rows * Large tables (more than 100 million rows) with only 1-2 columns heavily used

B. clustered columnstore. A clustered columnstore index is the most efficient type of index for querying large fact tables with a high proportion of aggregation queries. This is because a clustered columnstore index stores data in a columnar format, which is much more efficient for aggregation queries than a row-based format. Additionally, a clustered columnstore index stores data in a compressed format, which further reduces the amount of data that needs to be scanned. In this scenario, the fact table contains 50 columns and 5 billion rows, and most queries aggregate values from approximately 100 million rows and return only two columns. This indicates that the queries are primarily interested in summarizing the data in the table, rather than scanning the entire table for specific rows. Therefore, a clustered columnstore index is the most appropriate choice for improving the performance of these queries.

Clustered index * Tables with up to 100 million rows * Large tables (more than 100 million rows) with only 1-2 columns heavily used

f your table size is less than the recommended 60 million rows for clustered columnstore indexing, consider using heap or clustered index tables.

It asks which index to add answer c

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Question says already it's a heap table and asks what to add ? So answer is A

why not a NCCI - why not A ? Nonclustered columnstore index on a disk-based heap or B-tree index Use for: 1) An OLTP workload that has some analytics queries. You can drop B-tree indexes created for analytics and replace them with one nonclustered columnstore index. 2) Many traditional OLTP workloads that perform Extract Transform and Load (ETL) operations to move data to a separate data warehouse. You can eliminate ETL and a separate data warehouse by creating a nonclustered columnstore index on some of the OLTP tables. NCCI is an additional index that requires 10% more storage on average. R: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-ver16#choose-the-best-columnstore-index-for-your-needs Enjoy !

Only 2 columns returned

B of course, there are a few scenarios where clustered columnstore may not be a good option: Columnstore tables do not support varchar(max), nvarchar(max), and varbinary(max). Consider heap or clustered index instead. Columnstore tables may be less efficient for transient data. Consider heap and perhaps even temporary tables. Small tables with less than 60 million rows. Consider heap tables.

B. clustered columnstore index. Given the large fact table with 50 columns and 5 billion rows, and the fact that most queries aggregate values from approximately 100 million rows and return only two columns, a clustered columnstore index would be the most suitable choice. Clustered columnstore indexes are designed for large-scale data warehousing scenarios and provide excellent compression and query performance for analytical workloads. A clustered columnstore index stores the data in columnar format, enabling efficient data compression and batch-based query execution. It allows for significant query performance improvements, especially for aggregations and large-scale data retrieval.

im really baffled by all the answers here; noone is even considering clustered index, which is what microsoft is recommending for this particular user case scenario; https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/cheat-sheet#index-your-table for a table up to 100 mln records and using heavily 1-2 columns and performing queries with lots of joins and aggregations (group by clause) microsoft recommends clustered index; why is this recommendation not applicable here? could someone explain?

correct