You are not going to get accurate figures for space used back in time because of differences in how efficiently things are laid out in the pages as your data changes over time.
Your "inserted date" column will give you an estimate of the number of rows present, by taking the current row count and subtracting the count of rows inserted since the date you are measuring for (or just counting those inserted on/before that date). It may give an accurate reading rather than an estimate if:
- the table is never deleted from
- the audit date is only updated on insert not also on update
- any and all updates are performed with
UPDATE
or MERGE
and not DELETE
-then-reINSERT
.
You can use the row-count-at-a-date-in-the-past to estimate the total size of the pages it occupied by taking calculating (data_size_now * rows_then) / rows_now
.
That is for trying to look back in time when you haven't kept logs. Maintaining accurate records going forward is easy: setup a job that runs the analysis for each table and have it store the results for later review.