Exam DP-300 topic 6 question 31 discussion

A. COMPRESS function. It uses GZIP algorithm and typically is better for compressing text (and JSON) data, compared to columnstore archival compression.

the COMPRESS() function is not available in Azure Sql database

A. over B or D as columnstore compression is not available for nvarchar(max) datatypes.

Answer is B. Question is to minimize storage used. A and D will compress data but B will provide best compression. Question also says nothing about performance.

COMPRESS ( expression ) not only applies to Azure SQL DB but also supports the following expressions: binary(n) char(n) nchar(n) nvarchar(max) nvarchar(n) varbinary(max) varbinary(n) varchar(max) varchar(n) https://learn.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver16 - so it is the COMPRESS() function, available starting SQL 2016. Page and row compression DO NOT work on BLOBs, fyi. Neither is columnstore, it seems.

Selected Answer; D , To compress the JSON data in Column1 in the Azure SQL Database named SQLDb1 while minimizing storage usage, the recommended option would be D. columnstore compression. Columnstore compression is designed specifically for reducing the storage footprint of large datasets. It provides efficient compression algorithms that work well with columnar data storage, enabling significant storage savings. Columnstore compression organizes and compresses the data at the column level, resulting in improved query performance and reduced storage requirements. In this case, since you have JSON data in Column1, columnstore compression would be an appropriate choice. It will compress the data within the column, optimizing storage usage while maintaining query performance.

D. columnstore compression To minimize the amount of storage used while compressing Column1, you should use columnstore compression. Columnstore compression is specifically designed for columnar storage and provides high compression ratios for large datasets. It reduces the storage footprint by compressing and encoding the data within each column. This compression technique is well-suited for data warehousing and analytical workloads where the focus is on query performance rather than transactional operations. Therefore, using columnstore compression is the most appropriate option in this scenario.

https://learn.microsoft.com/es-es/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver16

To compress Column1, which contains JSON data, you should use the COMPRESS() function. This function can be used to compress data stored in an NVARCHAR(MAX) column1. So the correct answer is A. the COMPRESS() function.

Correct Answer: C ALTER TABLE dbo.Table1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW); GO