Newbie: Modifying Length for Image Data Type

  • I'm trying to increase the length for an image data type, but I've been unsuccessful in serveral attempts. The default length is 16. I have two questions that I hope someone out there knows the answer to:

    Is that 16 bytes?

    Where/how would I modify the data type for my image?

  • Thats correct. The image size is variable up to the max size of the field, pointer is always 16 bytes. The exception is with SQL2K if you have enabled the text in row option, you stil have the same limits but if the size is less than the cutoff point then the data is stored in the row, not external like it would be for normal text data. Brian Knight has an article on the site about the text in row if you're interested.

    Andy

  • This seems to be great. But why so? and what do you mean by cut off rate? Is it 8000 chars ie. the max varchar length.

    What I understand from the above lines from Andy is... If the text data is less than 8000 chars, sql server interprets the data as varchar instead of text and so is written onto the row instead of the pointer.

    quote:


    Thats correct. The image size is variable up to the max size of the field, pointer is always 16 bytes. The exception is with SQL2K if you have enabled the text in row option, you stil have the same limits but if the size is less than the cutoff point then the data is stored in the row, not external like it would be for normal text data. Brian Knight has an article on the site about the text in row if you're interested.

    Andy


    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • The cutoff point is whatever you set it. If you don't set it, then all only the pointer is stored in the table. If you look at sp_tableoption, you'll see this in BOL:

    When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row. When specified and @OptionValue is ON (enabled) or an integer value from 24 through 7000, new text, ntext, or image strings are stored directly in the data row. All existing BLOB (text, ntext, or image data) will be changed to text in row format when the BLOB value is updated. See Remarks section for more information.

    Andy

  • Thankx, shall check out BOL for more info.

    quote:


    The cutoff point is whatever you set it. If you don't set it, then all only the pointer is stored in the table. If you look at sp_tableoption, you'll see this in BOL:

    When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row. When specified and @OptionValue is ON (enabled) or an integer value from 24 through 7000, new text, ntext, or image strings are stored directly in the data row. All existing BLOB (text, ntext, or image data) will be changed to text in row format when the BLOB value is updated. See Remarks section for more information.

    Andy


    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

Viewing 5 posts - 1 through 4 (of 4 total)

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