Though I am very happy to see a question on a relatively new and very valuable option, I am a bit disappointed that the answers are debatable.
It all depends on what you mean when you say "affected".
- Restore and backup:
You could argue that this is simply copying pages from one place to another. What is on the page is irrelevant. The internal code to implement this is unchanged. Hence unaffected.
You could also argue that backup files of compressed databases are a lot smaller. Hence affected.
- Existing query plan:
I don't expect disagreement here. Indeed, all query plans will be re-optimized after enabling or disabling compression.
- Logshipping:
I *think* that even in a compressed database, the information in the log file is still uncompressed. If that is correct, than obviously logshipping is not affected, as the log file is not affected.
But if I am wrong, then the same reasoning applies as for the backup and restore. If compression makes the log file smaller, then you could argue that the code is unchanged (unaffected), or that you save a lot of bandwith (affected).
- Bulk import and export
I am frankly surprised by the remark in Books Online. Yes, obviously the exported data can be larger than the compressed database. D'uh! Should Books Online now also include a warning that printed reports might take more rainforests than the size of the data file suggests?
The internal code for the bulk operations are just as unaffected as any other code. SQL Server uncompresses the data when reading from disk to cache, and recompresses it when writing from cache to disk. Only a few SQL Server components ever access the disk directly, the cache manager (to read data into cache) and the lazy writer and checkpoint processes (to flush modified data from cache to disk) being the most important ones. All other components access cached data only. That includes the bulk operation. So this code is just as unaffected as the backup and restore code (that, incidentally, are two other components with direct access to disk - othwise, a backup of a compressed database would be just as large of the backup of an uncompressed one).
So thanks for making me think, and for enabling an interesting discussion. But for future QotD's (and I hope you will submit more), please try to make the question as answer as concise as possible, and to weed out any possible interpretation differences. ;-)
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog:
https://sqlserverfast.com/blog/SQL Server Execution Plan Reference:
https://sqlserverfast.com/epr/
