SQL: Getting the physical size of a subset of a ta

问题:

I'm getting the size of tables in my database using

sp_spaceused MyTable

Does anyone know a good way of finding out how much space only a certain subset of that data is taking up?
So, for example, if I want to find out how much space I would reduce by deleting all records from MyTable that are older than 2 years old. So instead of the space used by MyTable I would get the space used by (SELECT * FROM MyTable WHERE AddedDate < '2010').

Thanks

回答1:

Wouldn't it just be more or less exactly the percentage of the rows removed? So count the total number of rows, then count the number of rows that meet the condition, and do the division. Of course for that space to become fully usable you may need to rebuild table and/or index.

I suppose if you have var-length data and some rows are much larger than others then this approach may only be approximate but in most cases it should be good enough.

回答2:

Do you have the time and the space to create a new table using an INSERT INTO query with your WHERE clause? If so, you could do that and use sp_spaceused to check the size of the new table. It wouldn't be exact, but it should give you a pretty good idea.