Exam DP-203 topic 1 question 71 discussion

D. ALTER INDEX ALL on table1 REBUILD This statement will rebuild all indexes on table1, which can help to maximize columnstore compression. The other options are not appropriate for this task. DBCC INDEXDEFRAG (pool1, table1) is for defragmenting the indexes and DBCC DBREINDEX (table1) is for recreating the indexes. ALTER INDEX ALL on table1 REORGANIZE is for reorganizing the indexes.

Reorganizing an index is less resource intensive than rebuilding an index. For that reason it should be your preferred index maintenance method, unless there is a specific reason to use index rebuild. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

Definitely D. REORGANIZE is for light maintenance, to compact small rowgroups and clean up delta stores without a full rebuild. It's faster and less resource-intensive. REBUILD when the table has significant fragmentation or many delta stores. It recreates the entire index, providing MAXIMUM compression and performance improvement. Use for large-scale maintenance.

Correct Answer: D

Answer is C https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance?view=sql-server-ver16 If the number of deleted rows for several rowgroups is large enough to be merged into fewer rowgroups, reorganizing the columnstore increases the quality of the index and query performance improves. If your data deletion process usually empties entire rowgroups, consider using table partitioning

I saw all the documentations here and found another one that makes pretty clear the correct option is option D: "By default, tables are defined as a clustered columnstore index. After a load completes, some of the data rows might not be compressed into the columnstore. There's a variety of reasons why this can happen. To learn more, see manage columnstore indexes. To optimize query performance and columnstore compression after a load, rebuild the table to force the columnstore index to compress all the rows." https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-load-from-azure-data-lake-store#optimize-columnstore-compression

The answer is D. Why not C because ALTER INDEX ALL on table1 REORGANIZE is used for defragmenting the index, but it doesn’t achieve the same level of compression as a full rebuild.

To maximize columnstore compression for table1 in Azure Synapse Analytics dedicated SQL pool, you should execute the following statement: ALTER INDEX ALL on table1 REBUILD The REBUILD option is used to rebuild all indexes on the table, which will maximize columnstore compression for table1. This option is recommended when a large amount of data has been added to the table, as in this case where 5 TB of data has been loaded into table1

what is the different between "Alter Index Rebuild" or "DBCC DBREINDEX"

D. ALTER INDEX ALL on table1 REBUILD

D is correct

ALTER INDEX REORGANIZE is used for rebuilding or reorganizing indexes, but it does not maximize columnstore compression.

Reorganize is for row store indexes. The question here clearly mentions column store indexes. Correct answer is D

Selected Answer is C. reorganizing only help in optimizing compression and performance.

Why not C? When reorganizing a columnstore index, the Database Engine compresses each closed row group in delta store into columnstore as a compressed row group. Starting with SQL Server 2016 (13.x) and in Azure SQL Database, the REORGANIZE command performs the following additional defragmentation optimizations online: Physically removes rows from a row group when 10% or more of the rows have been logically deleted. For example, if a compressed row group of 1 million rows has 100,000 rows deleted, the Database Engine will remove the deleted rows and recompress the row group with 900,000 rows, reducing storage footprint.