Calculate size of the filtered rows of a table in sql server My requirement is to know the size occupied(count of records and size in mb) by a table during every 24 hours, I found query to get the whole size of a table but not sure on where to start with or how to achieve this.

I have an audit column(record_inserted_time) by which I can filter the records and get the count but how to know the size occupied during this period by the table(size of the filtered records).

I have to get this stats for around 100 tables all of which has the same audit column
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.