BenchMark Quizzes by MDFT Pro

Enable Page Compression On A Table

You work for MDFT Pro, a well-known training agency with an Azure SQL database named StudentData. Mark, the Database Performance Specialist, is working on optimizing storage costs and query performance. The database contains a table named TrainerProfiles with 20 columns of type CHAR(400) storing trainer biographies, certifications, and expertise descriptions. Row compression is currently enabled on the table. During a recent database audit, Mark discovered that none of the fields actually contain more than 150 characters, indicating significant wasted space. He wants to apply page compression to achieve better storage savings, but page compression is not currently working with the CHAR columns. Mark has asked you to make the necessary changes to enable page compression on the TrainerProfiles table.

What should you do?

Choose the correct answer from the options below.

Explanations for each answer:

Configure the columns as sparse is incorrect. Sparse columns are designed for storing NULL values efficiently but won't enable page compression for CHAR columns.
Change the column type to NVARCHAR(MAX) is incorrect. NVARCHAR(MAX) columns cannot be used with page compression and would actually prevent compression from being applied.
Change the column type to VARCHAR(MAX) is incorrect. VARCHAR(MAX) columns cannot be used with page compression, similar to NVARCHAR(MAX).
Change the column type to VARCHAR(200) is correct. Changing from CHAR(400) to VARCHAR(200) allows page compression to work effectively since VARCHAR uses variable-length storage and the data fits within the smaller size.

Learn more about Data Compression In SQL Server:

Data Compression

Practice Exam

Enable Page Compression On A Table

Discuss this question on social media: