Jeff Moden (12/7/2016)
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.
If the table is rebuilt, then the indexes must be rebuilt as well since they are bookmarked by row ids or clustering keys. So, ALTER TABLE <TableName> REBUILD will do this automatically, rebuilding non-clustered indexes in proper order within a single implicit transaction.
If this table is a heap, then you need to add a clustered key, even if it's just an INT IDENTITY(1,1) for the sole purpose of reorganization and space reallocation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho