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
DBCCupdateusage (‘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
Lets SQL 