SQL Server 2008 Enterprise moved forward on the compression capabilities that were introduced in SQL Server 2005 Service Pack 2. You should consider two compression technologies to reduce your database file size and to speed up your backups.

Data Compression

Data compression addresses some of the difficulties of working with very large databases by giving them a smaller footprint. By using this feature, you can selectively compress any table, table partition, or index. This results in a smaller on-disk footprint, smaller memory working-set size, and reduced I/O.

SQL Server 2008 supports two types of compression: row compression and page compression.

Row compression compresses the individual columns of a table. Row compression results in:

  • Lower overhead in application throughput
  • Less processor usage than page compression
  • Less space savings than page compression

Page compression compresses data pages by using row, prefix, and dictionary compression. Page compression results in:

  • Higher overhead in application throughput
  • More processor usage than row compression
  • More space savings than row compression

The amount of compression that is achieved is highly dependent on the data types and the data that are contained in the database. You will need to test with your specific data and schemas to determine the most effective compression techniques for your database system.

You should consider a hybrid approach, where only the largest tables are compressed, resulting in the best performance in terms of saving significant disk space while having a minimal negative impact on performance.

Data compression can be implemented through Transact-SQL or through the Data Compression Wizard. To determine how compressing an object will affect its size, you can use the sp_estimate_data_compression_savings system stored procedure or the Data Compression Wizard to calculate the estimated space savings. For more information about how to use compression, see Creating Compressed Tables and Indexes in SQL Server 2008 Books Online on MSDN.

Backup Compression

Backing up a large database can require a significant amount of time and a large amount of disk space for the backup files. With SQL Server 2008 backup compression, the backup file is compressed as it is written out, which may require significantly less storage, less disk I/O, and less time to take the backups. Again, this will depend on the data itself and how successfully SQL Server can perform compression on that data.

To achieve compression, you can specify the WITH COMPRESSION clause in the BACKUP command or select it on the Options page in the Back Up Database dialog box. There is also a global setting to compress all backups that are taken on a server instance by default. (You access this setting by using the Database Settings page of the Server Properties dialog box or by running sp_configure with backup compression default set to 1.) The restore command automatically detects that a backup is compressed and decompresses it during the restore operation. Note that any edition of SQL Server can restore a backup with compression, but only SQL Server Enterprise can create a compressed backup. For more information, see Tuning the Performance of Backup Compression in SQL Server 2008 on SQLCAT.com.

So, Should You Use Compression?

The answer is yes, as long as your ENOVIA V6 application supports SQL Server using compression and it makes sense for your application. Using compression on backups, for example, is almost certainly a win due to the reduction in time and space used by the backups.

Data compression may be more complicated. There may be specific tables or indexes that it makes sense to compress. You will likely need to test whether enabling compression actually improves your performance, because as noted above, it's not without overhead.