Size of Query Result
-
I am planning on archiving some old data from our Productions DB's. I have a question - is there anyway one can see how much space of the MDF a query result uses? That is lets say for example I go - select * from abc where a=123 and this returns 1000000 rows. I need to know how much space is used by what is returned in the result set. I don't think there is anyway to know - but just wondering if anyone has any ideas about even estimating it.
-
Make your query a SELECT INTO query and then use sp_spaceused 'tablename' to determine to determine the space used by the result set.
-
Thanks Darrel - already thought of that - unfortunately we are talking of millions of records - so not practical in this situation
-
I agree that this approach does not work with millions of rows, but I have addressed that by doing queries to select 1000, 10,000 and then 100,000 rows. With these result sets, I can evaluate the linearity of space consumption and extrapolate to a larger universe. In my experience, this extrapolation is accurate plus or minus 3%.
-
Ok thanks - sounds like a plan
-
Must admit this one made me curious. Did a little testing in the pubs database. Try something like this.
SELECT SUM(DATALENGTH(title_id)) +
SUM(DATALENGTH(title)) +
SUM(DATALENGTH(type)) +
SUM(DATALENGTH(pub_id)) +
SUM(DATALENGTH(price))
FROM pubs.dbo.titles
"Keep Your Stick On the Ice" ..Red Green
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply