Data Compression Interview Questions

1. Which of the following compression type is supported by SQL Server 2014 ?

A. Row

B. Column

C. Both row and column

D. None of the mentioned

Correct Answer is : Both row and column

2. Point out the correct statement :

A. The details of data compression are subject to change without notice in service packs or subsequent releases

B. Compression is not available for system tables

C. If you specify a list of partitions or a partition that is out of range, an error will be generated

D. All of the mentioned

Correct Answer is : All of the mentioned

3. Use _________ data compression to compress columnstore data with archival compression.

A. COLUMNSTOREARCHIVE

B. COLUMNSTORE_ARCHIVE

C. COLUMNSTORE

D. All of the mentioned

Correct Answer is : COLUMNSTORE_ARCHIVE

4. To remove archival compression and restore the data to columnstore compression :

A. Use ALTER TABLE

B. Use ALTER COLUMN

C. Use ALTER DATABASE

D. All of the mentioned

Correct Answer is : Use ALTER TABLE

5. Point out the wrong statement :

A. You can enable or disable ROW or PAGE compression in online state only

B. When you are compressing indexes, leaf-level pages can be compressed with both row and page compression

C. Non–leaf-level pages do not receive page compression

D. None of the mentioned

Correct Answer is : You can enable or disable ROW or PAGE compression in online state only

6. The data_compression and data_compression_desc columns include :

A. COLUMNSTOREARCHIVE

B. COLUMNSTORE_ARCH

C. COLUMNSTORE

D. None of the mentioned

Correct Answer is : COLUMNSTORE

7. Which of the following code is used to enable compression on a table ?

A. EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

B. EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

C. EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; ALTER TABLE Production.TransactionHistory BUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

D. EXEC sp_estimate_compression 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

Correct Answer is : EXEC sp_estimate_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW' ; ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

8. What is the purpose of schema name in the following code syntax ? sp_estimate_data_compression_savings [ @schema_name = ] 'schema_name' , [ @object_name = ] 'object_name' , [@index_id = ] index_id , [@partition_number = ] partition_number , [@data_compression = ] 'data_compression' [;]

A. Name of the table

B. Name of the database schema

C. Name of the indexed view

D. All of the mentioned

Correct Answer is : Name of the database schema

9. sp_estimate_data_compression_savings requires ______ permission on the table.

A. UPDATE

B. SELECT

C. DELETE

D. None of the mentioned

Correct Answer is : SELECT

10. Which of the following query estimates the size of the Production.WorkOrderRouting table if it is compressed by using ROW compression ?

A. EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, NULL, ‘ROW’ ;

B. EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, 1, ‘ROW’ ;

C. EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, 1, NULL, ‘ROW’ ;

D. EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, NULL, ‘COLUMN’ ;

Correct Answer is : EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, NULL, ‘ROW’ ;

Data Compression interview questions