RE: Database Size After Deleting 200-million rows

SSC Guru

Points: 1003873

December 7, 2016 at 5:09 pm

#1916655

Eric M Russell (12/7/2016)
alex.sqldba (12/7/2016)
Hi Eric,
Whilst it is not a heap, there is a clustered index on a datetime field, I am curious as to how one would go about reclaiming the space if it was in fact a heap?
Would you have to add a clustered index to deal with it?
This will rebuild table and indexes in one atomic operation.
ALTER TABLE <TableName> REBUILD;

I could be wrong but the documentation on ALTER TABLE leads me to believe that it will either rebuild a HEAP or just the Clustered Index on a table and that it won't actually rebuild the Non-Clustered Indexes. Haven't actually tried it, though, so can't say for sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)