Compressions vs shrinking a database

Question

Is compression and shrinking a database in SQL Server same? If I compress a table, will it decrease query performance and DML?

Answer 1

In SQL Server, shrinking a database/datafile and compression are not the same. Shrinking a file (which, by the way, isn't really recommended) is the process of removing unused space from data files in your database. When files are created, SQL Server "reserves" space by sizing files out (depending on how the file is created), even if it doesn't actually have data to fill that space up. The idea is that you'll eventually put something in there (but not always). Shrinking removes this empty space.

Data compression, on the other hand, compresses your objects to reduce the space within the data file they take up. There are a couple different types of compression, but what they all end up doing is reducing the physical space required by your tables and indexes.

Consider you have a 1 GB data file with 600 MB worth of actual data in it. The two operations will have the following affects:

• Shrink: You will resize the data file down to 600 MB. This is the limit, because you still need capacity for the data.
• Compress: You will resize your data from 600 MB to some amount less (depending on how good the algorithms can compress it), but your data file will remain 1 GB.

To your second question, compression is more CPU intensive(it has to uncompress the data to make use of it), but uses less disk IO (fewer bits to pull off the disk). It might affect your query performance if you are CPU constrained. The only way to properly evaluate is to test.