Post reply

Size of Query Result

  • April 13, 2004 at 3:00 pm

    #82555

    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.

     

  • April 14, 2004 at 1:11 am

    #502660

    Make your query a SELECT INTO query and then use sp_spaceused 'tablename' to determine to determine the space used by the result set.

  • April 14, 2004 at 8:51 am

    #502747

    Thanks Darrel - already thought of that - unfortunately we are talking of millions of records - so not practical in this situation

  • April 14, 2004 at 11:44 am

    #502790

    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%.

  • April 14, 2004 at 11:47 am

    #502791

    Ok thanks - sounds like a plan

  • April 14, 2004 at 3:00 pm

    #502818

    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