SQL Interview: 38 Table Compression Candidates

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

A developer in your team has suggested applying PAGE compression to all tables and indexes in your application database.

You are concerned that doing so might impact performance, and that ROW compression might be better.

What guideline could you provide for deciding this?

Answer:

A good general guideline is to start with ROW compression for all tables and indexes.

Then, apply PAGE compression where the data is scanned more than 80% of the time, and updated less than 20% of the time.

NOTE: if you have partitioned tables/indexes, this decision should be made for each partition, not just for the entire table/index.

2025-04-19