Depending on the SAP and SQL Server release, there are different compression types supported by SAP NetWeaver. Unfortunately the naming convention used by SAP and Microsoft is not consistent. Therefore there was some confusion about this in the past. In SAP notes the following compression types are mentioned: database compression, data compression, row compression, page compression, UCS-2 compression (aka unicode compression), index compression, vardecimal storage format (aka decimal compression or vardecimal data type)
SQL Server compression types
Any type of compression currently requires the Enterprise or Datacenter Edition of SQL Server. The Developer Edition also works fine, but is not supported by SAP for productive usage. The following SQL Server releases introduced new compression features:
- SQL Server 2005 SP2
In SQL Server 2005 SP2 the vardecimal storage format was released. This is a subset of the row compression type, which was released in SQL Server 2008. Due to the restrictions of the vardecimal storage format it should be considered as deprecated.
- SQL Server 2008
Row compression and page compression were released in SQL Server 2008. A table or index has one of the following compression types:
o NONE: The table or index is not compressed.
o ROW: All rows are stored in variable length. This compression type has no CPU overhead, but saves disk space and reduces disk I/O.
o PAGE: Page compression is always performed on top of row compression. It even saves more disk space. However, there might be a higher CPU load on the database server.
Whether you compress the data (heap, clustered index) or the (non-clustered) indexes: The feature is called data compression . The term “index compression” is not used by Microsoft.
The term “database compression” is not well-defined. It is often used for any kind of compression (data compression or vardecimal storage format).
- SQL Server 2008 R2
SQL Server uses UCS-2 (not UTF-8) to encode Unicode characters. SQL Server 2008 R2 stores row or page compressed UCS-2 characters more efficient than SQL Server 2008. The space usage of a UCS-2 character is roughly the same as a UTF-8 character would need, once a table is row or page compressed. This feature is called UCS-2 compression or simply unicode compression. However, you may consider it as an improved data compression, not as a new compression type.
Supported compression types by SAP
Depending on the SAP release, the following compression types are supported for an ABAP application server. For a JAVA application server there is currently no compression support.
- Vardecimal storage format
The vardecimal storage format was used by SAP only for BW fact tables. The indexes of BW fact tables did never benefit from it. As of SQL Server 2008, new BW fact tables are not created using the vardecimal storage format any more, since there is no advantage of it compared to row compression. SAP considers the vardecimal storage format as a deprecated feature.
- Row compression for tables (heap or clustered index)
Before September 2010 the SAP Data Dictionary created new tables on SQL Server 2008 with compression type ROW and all new non-clustered indexes with compression type NONE. When converting an existing table manually in SAP transaction SE14 or during an SAP upgrade, the compression type of a table was not changed (it stayed NONE, ROW or PAGE). However, non-clustered indexes were always created with compression type NONE.
- Row compression for (non-clustered) indexes and
Page compression for tables and indexes as of September 2010
In September 2010 SAP released correction instructions in SAP note 1459005 for SAP Basis 7.00 and all newer releases. These corrections will be included in future SAP BASIS support packages. With the corrected SAP data dictionary all new indexes will be created with the same compression type as their table. In addition you can change the default compress ion type for new tables, for example to page compression. SAP calls the new feature to compress indexes (as row or page compressed) index compression . This is actually not a SQL Server compression type. It is simply the name of an SAP feature.
Conclusion
You need at least SQL Server 2008 for data compression (row or page compression). To benefit from UCS-2 compression you need at least SQL Server 2008 R2. Therefore you should upgrade to SQL Server 2008 R2. All SAP NetWeaver releases supported on SQL Server 2008 are also supported on SQL Server 2008 R2.
Upgrade your SAP system at least to release 7.00 and apply SAP note 1459005 . As a result you can use index compression. Use the report MSSCOMPRESS to compress existing tables and indexes. The report is attached to SAP note 1488135 .
When starting to apply SQL Server data compression, use row compression first. This compression type does not increase the CPU resource consumption. However, the deployment of page compression should be planned based on your available CPU resources on the database server.
Do not use the vardecimal storage format any more. It is a deprecated feature and only used in a very limited way by SAP. SAP note 991014 describes the usage of the vardecimal storage format in detail.
