Professional Web Applications Themes

BLOBS & Database Space - Microsoft SQL / MS SQL Server

An column of data type "image" stores the actual image, not just a pointer. So, if you delete a row, it will free up the space so that it can be utilized by new rows. regards, -marty nicholson "Rocky" <> wrote in message news:010201c3513d$bb7fb6b0$gbl... ...

  1. #1

    Default Re: BLOBS & Database Space

    An column of data type "image" stores the actual image, not just a pointer.
    So, if you delete a row, it will free up the space so that it can be
    utilized by new rows.

    regards, -marty nicholson

    "Rocky" <> wrote in message

    Martin Guest

  2. #2

    Default Re: BLOBS & Database Space

    Storing blob in the database can really bloat it. Simply deleting a row does
    not guarantee the diskspace to be releases back to the OS. You will have to
    shrink the database to be certain. Please take a look at dbcc
    shrinkdatabase/dbcc shrinkfile in sql book online for some info.

    RAC v2.2 & QALite!

    "Rocky" <> wrote in message

    oj Guest

  3. #3

    Default Re: BLOBS & Database Space


    It normally stores a pointer to another page, not the binary data itself --
    although you can control this to some degree -- see the sp_tableoption and
    'table in row'. (See excerpt below.)
    If your blobs are always >8k (as is often the case) you don't have any
    control of where they get stored, and the # of records per page will probaby
    get too small for good performance before that.

    So, nobody else having a >64K BLOB problem w/Ado.Net & SQL7/2000?

    Using text in row to Store text, ntext, and image Values
    Usually, text, ntext, or image strings are large (a maximum of 2GB)
    character or binary strings stored outside a data row. The data row contains
    only a 16-byte text pointer that points to the root node of a tree built of
    internal pointers that map the pages in which the string fragments are

    With Microsoft SQL Server, you can store small to medium text, ntext, and
    image values in a data row, thereby increasing the speed of queries
    accessing these values.

    When the text, ntext, or image string is stored in the data row, SQL Server
    does not have to access a separate page or set of pages to read or write the
    string. This makes reading and writing the text, ntext, or image in-row
    strings about as fast as reading or writing varchar, nvarchar, or varbinary

    To store text, ntext, or image strings in the data row, enable the text in
    row option using the sp_tableoption stored procedure.

    sp_tableoption N'MyTable', 'text in row', 'ON'Optionally, you can specify a
    maximum limit, from 24 through 7000 bytes, for the length of a text, ntext,
    and image string stored in a data row:

    sp_tableoption N'MyTable', 'text in row', '1000'If you specify 'ON' instead
    of a specific limit, the limit defaults to 256 bytes. This default value
    provides most of the performance benefits: It is large enough to ensure that
    small strings and the root text pointers can be stored in the rows but not
    so large that it decreases the rows per page enough to affect performance.

    Although in general, you should not set the value below 72, you also should
    not set the value too high, especially for tables where most statements do
    not reference the text, ntext, and image columns or there are multiple text,
    ntext, and image columns.

    You can also use sp_tableoption to turn the option off by specifying an
    option value of either 'OFF' or 0:

    sp_tableoption N'MyTable', 'text in row', 'OFF'

    "Martin Nicholson" <> wrote in
    message news:O25SR%phx.gbl... 

    Michael Guest

  4. #4

    Default Re: BLOBS & Database Space

    "Martin Nicholson" <> wrote in
    message news:phx.gbl... 

    Good for you. I'm not going to start a lengthy debate on blob since there
    have been many on this forum already. Yes, there are adv/disadv on either
    side. A quick google should show you this.

    Since you're replying to my post, here are some comments inline.

    better security/centralized storage I can accept. However, I would really
    question 'faster backup'. The amount of data for all your blobs doesn't
    change whether you store it in the db or filesys. Backing up the same amount
    of bytes requires the same amount of time. Also, I would be really
    interested to see how you could index a ntext/text/image column. This would
    surely be the greatest hack in sqlserver then.

    One doesn't really have to get a custom SW. There is nothing wrong with
    using built-in ReadText/UpdateText/WriteText.


    Again, the amount of time for backing up is relative. The major disadv is
    really the lack of centralized management. Your files could scatter all over
    the drive in many different folders/subfolders. Moving them could be a
    challenge as you might leave some behind.



    oj Guest

Similar Threads

  1. Newbie: How to add Non-breaking Space in a database field?
    By in forum MySQL
    Replies: 2
    Last Post: March 6th, 08:24 PM
  2. Pulling BLOBs from Database
    By jl_forum in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 30th, 08:05 AM
  3. Replies: 2
    Last Post: December 15th, 01:48 PM
  4. database managed space
    By xixi in forum IBM DB2
    Replies: 3
    Last Post: July 31st, 06:01 AM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139