New data compression - Columnstore Archive

All who have used columnstore index must know that columnstore index also compress data.
Many cases after some time this data become historical and use of this data reduces over time.
In this case we can further compress data sung columnsore_archive compression option. Retrieving data takes time after this compression but its acceptable as it reduces storage and its usage frequency is low compare to current data

Lets see with example



CREATE TABLE test
  (
     id   INT,
     data VARCHAR(max)
   
  )

DECLARE @i INT

SET @i = 1

WHILE @i <= 100000
  BEGIN
      INSERT INTO test
      VALUES      (@i,
                   'The value of i is '
                   + CONVERT(VARCHAR(10), @i))

      SET @I = @i + 1
  END

SP_SPACEUSED test

Normal Table


Now we will create clustered columnstore index here.
It will compress data

 
CREATE CLUSTERED COLUMNSTORE INDEX c1 ON test

Table with clustered columnstore index









Now we will compress more using columnstore_archive option 
This will compress data even more. 


ALTER INDEX c1 ON test REBUILD WITH (data_compression = columnstore_archive)

Table with columnstore_archive compression option

 
 



Here we can see size reduces from 8848 KB to 2704 KB and then 1232 KB.


1 comment:

Post a Comment

Amazon Contextual Product Ads

Popular Posts