Compression occurs in the storage engine and the data is presented to most of the other components of SQL Server in an uncompressed state. This limits the effects of compression on the other components to the following:

a) Bulk import and export operations

When data is exported, even in native format, the data is output in the uncompressed row format. This can cause the size of exported data file to be significantly larger than the source data.

When data is imported, if the target table has been enabled for compression, the data is converted by the storage engine into compressed row format. This can cause increased CPU usage compared to when data is imported into an uncompressed table.

When data is bulk imported into a heap with page compression, the bulk import operation will try to compress the data with page compression when the data is inserted.

b) Compression does not affect backup and restore.

c) Compression does not affect log shipping but do affect replication.

Enabling compression can cause query plans to change because the data is stored using a different number of pages and number of rows per page.