[ @schema_name = ] 'schema_name'

Is the name of the database schema that contains the table or indexed view. schema_name is sysname. If schema_name is NULL, the default schema of the current user is used.

[ @object_name = ] 'object_name'

Is the name of the table or indexed view that the index is on. object_name is sysname.

[ @index_id = ] 'index_id'

Is the ID of the index. index_id is int, and can be one of the following values: the ID number of an index, NULL, or 0 if object_id is a heap. To return information for all indexes for a base table or view, specify NULL. If you specify NULL, you must also specify NULL for partition_number.

[ @partition_number = ] 'partition_number'

Is the partition number in the object. partition_number is int, and can be one of the following values: the partition number of an index or heap, NULL or 1 for a nonpartitioned index or heap.

To specify the partition, you can also specify the $partition function. To return information for all partitions of the owning object, specify NULL.

[ @data_compression = ] 'data_compression'

Is the type of compression to be evaluated. data_compression can be one of the following values: NONE, ROW, or PAGE.

0 (success) or 1 (failure)

The following result set is returned to provide current and estimated size for the table, index, or partition.

Use sp_estimate_data_compression_savings to estimate the savings that can occur when you enable a table or partition for row or page compression. For instance if the average size of the row can be reduced by 40 percent, you can potentially reduce the size of the object by 40 percent. You might not receive a space savings because this depends on the fill factor and the size of the row. For example, if you have a row that is 8000 bytes long and you reduce its size by 40 percent, you can still fit only one row on a data page. There is no savings.

If the results of running sp_estimate_data_compression_savings indicate that the table will grow, this means that many rows in the table use almost the whole precision of the data types, and the addition of the small overhead needed for the compressed format is more than the savings from compression. In this rare case, do not enable compression.

If a table is enabled for compression, use sp_estimate_data_compression_savings to estimate the average size of the row if the table is uncompressed.

An (IS) lock is acquired on the table during this operation. If an (IS) lock cannot be obtained, the procedure will be blocked. The table is scanned under the read committed isolation level.

If the requested compression setting is same as the current compression setting, the stored procedure will return the estimated size with no data fragmentation and using the existing fill factor.

If the index or partition ID does not exist, no results are returned.

Requires SELECT permission on the table.

The following example estimates the size of the Production.WorkOrderRouting table in the AdventureWorks database if it is compressed by using ROW compression.

USE AdventureWorks
GO
EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW' ;
GO

Reference

Help and Information