In my previous article, I am explaining about the Data compression mechanisms. How the data compression Works. In this article I am illustrating about the Step By Step Procedure of Data Compression by T-SQL Statement
.
We can configure the data compression in 2 ways
1. Configure through T-SQL
2. Configure through GUI
Estimating the Compression ratio
Sp_estimate_data_compression_saving
Returns the current size of the requested objects and estimate the object size for the requested compression state. Compression can be evaluated for whole tables or part of tables. This includes heaps, clustered indexes, nonclustered indexes, indexed views, and table and index partitions. The objects can be compressed by using row compression or page compression. If the table, index, or partition is already compressed, you can use this procedure to estimate the size of the table, index, or partition if it is recompressed.
The syntax is mentioned bellow
sp_estimate_data_compression_savings
[ @schema_name = ] 'schema_name'
, [ @object_name = ] 'object_name'
, [@index_id = ] index_id
, [@partition_number = ] partition_number
, [@data_compression = ] 'data_compression'
[;]
Return "0" for success and "1" for failure.
Example:
USE AdventureWorks2012;
GO
EXEC sp_estimate_data_compression_savings
'Production',
'WorkOrderRouting',
NULL,
NULL,
'ROW' ;
GO
Return Result Set.
Column name
|
Data type
|
Description
|
object_name
|
sysname
|
Name of the table or the indexed view.
|
schema_name
|
sysname
|
Schema of the table or indexed view.
|
index_id
|
int
|
Index ID of an index:
0 = Heap
1 = Clustered index
> 1 = Nonclustered index
|
partition_number
|
int
|
Partition number. Returns 1 for a non partitioned table or index.
|
size_with_current_compression_setting (KB)
|
bigint
|
Size of the requested table, index, or partition as it currently exists.
|
size_with_requested_compression_setting (KB)
|
bigint
|
Estimated size of the table, index, or partition that uses the requested compression setting; and, if applicable, the existing fill factor, and assuming there is no fragmentation.
|
sample_size_with_current_compression_setting (KB)
|
bigint
|
Size of the sample with the current compression setting. This includes any fragmentation.
|
sample_size_with_requested_compression_setting (KB)
|
bigint
|
Size of the sample that is created by using the requested compression setting; and, if applicable, the existing fill factor and no fragmentation.
|
Configuring data compression using page-level compression
ALTER TABLE [dbo].[Compression_Test] REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE -- Specify the compression type here
)
Compression effect with partitionTable
· When partitions are split by using the ALTER PARTITION statement, both partitions inherit the data compression attribute of the original partition.
· When two partitions are merged, the resultant partition inherits the data compression attribute of the destination partition.
· To switch a partition, the data compression property of the partition must match the compression property of the table.
· There are two syntax variations that you can use to modify the compression of a partitioned table or index:
ALTER TABLE <table_name>
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = <option>)
The following syntax rebuilds the whole table by using the existing compression setting for any partitions that are not referenced:
ALTER TABLE <table_name>
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
... )
Verify the compression setting for the table
SELECT partition_id,
object_name(object_id) ObjectName,
data_compression_desc Data_Compression_Type
FROM sys.partitions
WHERE data_compression <>0
In my next article I am discussing about GUI method…..
Hope you like it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment