Is compression and shrinking a database in SQL Server same? If I compress a table, will it decrease query performance and DML?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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.