Support for SQL Server data compression

  • Article

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Note

These release notes describe functionality that may not have been released yet. To see when this functionality is planned to release, please review What's new and planned for Dynamics 365 Business Central. Delivery timelines and projected functionality may change or may not ship (see Microsoft policy).

As of Business Central April 2019, the use of SQL Server data compression is a supported configuration.

You can use data compression to help reduce the size of selected tables in the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries have to read fewer pages from disk. This is especially useful if your storage system is based on disks and not SSD.

By using the CompressionType property in AL, you can either configure compression in metadata for a table, and let the Business Central table synchronization process handle the changes to SQL Server tables, or you can choose to control data compression directly on SQL Server.

Because SQL Server supports data compression on the partition level, you can combine SQL Server data compression with table partitioning to achieve flexible data archiving on historical parts of a large table without having the CPU overhead on the active part of the table.

Note

Prior to SQL Server 2016 SP1, compression was not available in every edition of SQL Server.

Tell us what you think

Help us improve Dynamics 365 Business Central by discussing ideas, providing suggestions, and giving feedback. Use the Business Central forum at https://aka.ms/businesscentralideas.