Exam DP-300 topic 1 question 40 discussion

My answer: D Used below code to test if nonclustered index can be created on a table that has partitioned clustered columnstore index with archival compression: CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE RIGHT FOR VALUES (1, 2, 3) ; CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 ALL TO ('PRIMARY') ; GO create table Test_Columnstore_Archival ( ID int, tName varchar(5)) on myRangePS1 (ID) insert into Test_Columnstore_Archival (ID, tName) values (1,'aaa'),(2,'bbb'),(3,'ccc') create clustered columnstore index CCIX_ID on Test_Columnstore_Archival order (ID) with (data_compression=columnstore_archive) create nonclustered index IX_tName on Test_Columnstore_Archival (tName) with (data_compression=page) select * from Test_Columnstore_Archival exec sp_BLitzIndex @databasename = 'MyDB', @tablename = 'Test_Columnstore_Archival'

D correct

https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver16

• For partitioned columnstore tables and columnstore indexes, you can configure the archival compression option for each partition, and the various partitions don't have to have the same archival compression setting. Archival compression would be correct

D -Columnstore archival compression is a feature of Azure SQL Database and SQL Server that allows for maximum compression of columnstore index data. It is specifically designed for large datasets and is ideal for read-heavy workloads where maximizing compression is crucial.

The correct Answer is B A clustered columnstore index (CCI) will compress your data and maximize the compression ratio. It also allows you to index the compressed data.

I think the B. columnstore compression is better answer, who is sure about this answer ?

Columnstore tables and indexes are always stored with columnstore compression ¹. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. To perform archival compression, SQL Server runs the Microsoft XPRESS compression algorithm on the data ¹. However, it is important to note that archival compression is not an indexing technique. Important (Only when you can afford extra time and CPU) Archival compression is a data compression technique that can be used to further reduce the size of columnstore data for situations when you can afford extra time and CPU resources to store and retrieve the data.

While columnstore archival compression can be used to compress columnstore data in a way that minimizes storage costs, it is not optimized for query performance, as it is designed for long-term storage of cold data. In this scenario, the requirement is to maximize compression ratio while still being able to perform index operations on the compressed data. Columnstore archival compression is not optimized for indexing, and as such, it would not meet this requirement. Therefore, the correct answer is B. columnstore compression.