Data Compression — Complete Guide
Data Compression — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of SQL Server Tutorial on Toolliyo Academy.
On this page
SQL Server Tutorial · Lesson 69 of 100
Data Compression
SQL basics ✓ → Queries ✓ → Advanced
Advanced · 3 — Procedures · ~10 min · SQL — Advanced SQL Server
What is this?
ROW or PAGE compression stores table/index data more densely to save space and sometimes reduce IO at the cost of CPU.
Why should you care?
Large historical tables benefit when disk and backup size dominate cost.
See it live — copy this example
Run in SQL Server Management Studio (SSMS) or Azure Data Studio.
USE DataVerse;
ALTER TABLE dbo.FactOrderDaily REBUILD WITH (DATA_COMPRESSION = PAGE);
SELECT p.data_compression_desc, i.name
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.object_id = OBJECT_ID(N'dbo.FactOrderDaily');What happened?
- REBUILD applies PAGE compression to the table’s indexes/partitions.
- Catalog views confirm the setting.
- Test CPU vs IO on your workload.
Practice next
- Compress FactOrderDaily with PAGE in lab.
- Verify data_compression_desc.
- Run an aggregate and note CPU time vs before.
- Try DATA_COMPRESSION = ROW and compare size.
- sp_estimate_data_compression_savings in lab.
Remember
ROW/PAGE compression save space. Trade CPU for IO. Measure before rolling out widely.
Archive fact compression
DataVerse old fact partitions use PAGE compression.
Outcome: Backups shrink; queries stay acceptable.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
