Compression

Question 1

What are the negatives with compression?

Answer 1

Additional time is spent getting data, so you lose speed with compression.

Question 2

What does Row Compression do for you?

Answer 2

• Reduces metadata overhead
• Uses variable-length storage for numeric-based types
• Uses variable-length character strings

Question 3

What is Prefix Compression

Answer 3

Stores commonly-used prefixes elsewhere

Prefix values are replaced by a reference to the prefix

Question 4

What is Dictionary compression?

Answer 4

Replaces commonly used values. (within a page)

Question 5

What compression is included with Page Compression?

Answer 5

Row Compression
Prefix Compression
Dictionary Compression
(In order)

Question 6

What is the t-sql to create compression on a table?

Answer 6

ALTER TABLE [TABLENAME] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

Question 7

What is the t-sql to get how much data would be saved with compression?

Answer 7

exec sp_estimate_data_compression_savings

‘schema’,’table’, indexid, null, ‘PAGE’

Question 8

What is Sparse for a column?

Answer 8

It allows the database to save space with NULL values, but it will use more space with non nulls.
40-60% of values need to be null for this to be effective.

Question 9

What is a columnstore?

Answer 9

It has a high level of data compression as well as greater speed.

rowstore works better for seeks
columnstore works better for scans (large tables - 200k+ rows)

Table can only have 1 nonclustered columnstore index