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?
