Post reply

BLOB Storage

  • October 9, 2003 at 12:12 pm

    #82797

    I have a question regarding the storage of Word Docs in a table as a BLOB data type, specifically size. I am doing some testing size comparisons between storaging documents in the database as opposed to the file system. I have 6 documents saved to disk at a total size of 284kb when stored to a seperate data file on SQL Server 2000 Enterprise edition the data file size is 768kb. Is this right? Does anyone know why the extra memory use?

  • October 9, 2003 at 4:59 pm

    #477777

    Have a look at "Text in Row Data" in Books Online.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • October 9, 2003 at 7:27 pm

    #477787

    The actual space used should be roughly equivalent. I think you are saying that the SQL Server data files that hold the BLOBs are larger than the sum of the native files on the disk. Remember that:

    • There will usually be unused space in the database files.
    • All objects in the database (meta data, indexes, stored procedures, etc.) are stored in its data file(s); that's more than just the data.
    • There is overhead involved (pointers, granularity waste, bitmaps, etc.) in organizing and optimizing the use of data in the database.

    If your BLOBs are in one table, and are the only data in the table, you can get an idea of how much space they are actually using with this system stored procedure:

    

    EXEC sp_spaceused 'TableName'

    I think you'll find that the actual space used will be close to the file sizes.

    --Jonathan



    --Jonathan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply