Alter table rebuild option for data_compression

For table which one is stored as a heap we can now reclaim free space from this tables using alter table rebuild option.
lets see



use test

CREATE TABLE tbl

    (

         id   INT IDENTITY (1, 1),

         data VARCHAR(100)

    )

DECLARE @i INT

SET @i = 1

WHILE @i <= 10000

    BEGIN

        --delete top (1) from test

        INSERT INTO tbl

        VALUES     ('a')

        SET @i = @i + 1

    END

DBCC updateusage ('test', 'tbl')

EXEC Sp_spaceused tbl

name rows reserved data index_size unusedtbl 10000 136 KB 120 KB 8 KB 8 KB

Now delete 5000 rows from this table.

set @i = 1
while @i <= 5000
begin
delete top (1) from tbl
set @i = @i + 1
end

dbcc updateusage ('test','tbl')
exec sp_spaceused tbl

name rows reserved data index_size unused
tbl 5000 136 KB 120 KB 8 KB 8 KB

We can see the space is not free after delete operation completed

Now we will compress the table.

ALTER TABLE tbl
REBUILD WITH (DATA_COMPRESSION = page);

exec sp_spaceused tbl

name rows reserved data index_size unused
tbl 5000 80 KB 64 KB 8 KB 8 KB

Now we can see the space is now return to the database.

* Similarly if the table has clustered index it will rebuild the clustered index during alter table rebuild operation

* While creating a table we can also specify data_compression option on the table.


create table tbl
(id int identity (1,1),
data varchar(100))
with (data_compression = row)

but on heap data compression will only work on
alter table rebuild , bulk_insert on insert into .... with (tablock) option

*If we have clustered index then we can also specify data_compression on index

create clustered index idx_clst on tbl(data) with (data_compression = row)

*data_compression has some limitation like size of row should not be more then 8060 bytes and some others but overall its a cool feature