In a SQL Server context, native compression refers to data compression performed by factory-installed software rather than third party software. SQL Server offers both native backup compression and native data compression. Until 2008, data compression in SQL Server could only be undertaken with the aid of third-party software.
Native compression is generally preferred as it is assumed that native methods are more efficient and compatible than third-party software. The general reasoning is that software specifically designed to work in a particular environment (such as SOL Server) written by the original developers is going to create the smallest possible file in the least amount of time.
That said, new and improved methods for compression have been developed over the last few years, and using tools such as Idera’s SQL Safe allows you to compress files much smaller in a lot less time than with native compression.
According to the SQL Safe datasheet, “SQL Safe saves money by reducing database backup time by up to 50% over native backups and reducing backup disk space requirements by up to 95%.”
Aside from saving disk space, compressing data also offers the benefit of improved database performance. Compressed data is stored on fewer pages, therefore queries read fewer pages from the disk, which can result in significant performance improvements in I/O-intensive environments.
The downside to data compression is that additional CPU resources are needed to compress and decompress the data when data is exchanged with the application. That’s why it’s essential to have a firm grip on workload characteristics before making decisions about which tables to compress.