0

I will be deleting 40% of rows from a table that currently has 2.5 millions rows, space reserved 17 GB, 150 MB index, unused space 3 MB.

The primary key is the identity, and records were inserted in order. There are 2 non-clustered indexes. The rows I delete are the oldest. There is a FK pointing to the table, but it is not a problem because it only refers to recent records (which will not be deleted). Columns are mostly nvarchar, including a few nvarchar(max).

The actual deletion will take less than 10 minutes, the database server will not be used by anyone else for a couple of hours, so I am not worried by the deletion itself.

To get the maximum advantage from the point of view of disk size and performances, which operations can I do after the deletion? I don't want to create a new table and move the data, but is there a way I can rearrange the data and reclaim the space?

  • If this is a common task, consider partitioning the table. Drop Partion takes seconds. – Michael Kutz May 28 at 10:16
1

Rebuild the table

ALTER TABLE yourtablename REBUILD

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.