locked
Question about Data Types RRS feed

  • Question

  • Hello Everyone,

    I have a quick question.

    [Using SQL Server 2005]

    I am creating an Images table in my database, and not really sure which datatype to use.. Should I use varbinary or image?



    Also, if I am to use varbinary, should the length be set to max?  We dont want users storing big images in the database, nothing large than say 250kb.


    Any suggestions?

    Thanks!!

    ~Matt
    Tuesday, February 23, 2010 12:39 AM

Answers

  • Hi,

    IMAGE data types will be removed in a future version of Microsoft SQL Server.
    I would recommend using varbinary to avoid future changes.
    • Marked as answer by intertek Tuesday, February 23, 2010 3:35 PM
    Tuesday, February 23, 2010 6:34 AM

  • I am creating an Images table in my database, and not really sure which datatype to use.. Should I use varbinary or image?



    Also, if I am to use varbinary, should the length be set to max?  We dont want users storing big images in the database, nothing large than say 250kb.


    Any suggestions?

    Thanks!!

    ~Matt

    You should use VARBINARY(250)... Try this sample code

    DROP

     

    TABLE #TESTTAB

    CREATE

     

    TABLE #TESTTAB (DOCLEN BIGINT, DOC VARBINARY(250))

    INSERT

     

    INTO #TESTTAB

    SELECT

     

    LEN(BULKCOLUMN), *

    FROM

    OPENROWSET

     

    (BULK 'C:\TEST1.TXT', SINGLE_BLOB)

    AS

     

    R

    SELECT

     

    *,DATALENGTH(DOC)

    FROM

     

    #TESTTAB

     

    Take  a text file keep the size less than 250 , it will insert, when the size is greater than 250 byte it will throw

    Msg 8152, Level 16, State 10, Line 3

    String or binary data would be truncated.

    The statement has been terminated.


    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by intertek Tuesday, February 23, 2010 3:35 PM
    Tuesday, February 23, 2010 7:01 AM
  • VARBINARY(250) means a VARBINARY of length 250 bytes, the poster wants to accomodate 256000bytes (250KB).

    From binary and varbinary (Transact-SQL) :
    Use varbinary(max) when the column data entries exceed 8,000 bytes.

    If you want to limit the size of the images being stored then I suspect you may have to do it in your application. This is the correct thing to do anyway, you shouldn't rely on a truncation error as an implementation of business logic.

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    • Marked as answer by intertek Tuesday, February 23, 2010 3:35 PM
    Tuesday, February 23, 2010 9:38 AM

All replies

  • Hi,

    IMAGE data types will be removed in a future version of Microsoft SQL Server.
    I would recommend using varbinary to avoid future changes.
    • Marked as answer by intertek Tuesday, February 23, 2010 3:35 PM
    Tuesday, February 23, 2010 6:34 AM

  • I am creating an Images table in my database, and not really sure which datatype to use.. Should I use varbinary or image?



    Also, if I am to use varbinary, should the length be set to max?  We dont want users storing big images in the database, nothing large than say 250kb.


    Any suggestions?

    Thanks!!

    ~Matt

    You should use VARBINARY(250)... Try this sample code

    DROP

     

    TABLE #TESTTAB

    CREATE

     

    TABLE #TESTTAB (DOCLEN BIGINT, DOC VARBINARY(250))

    INSERT

     

    INTO #TESTTAB

    SELECT

     

    LEN(BULKCOLUMN), *

    FROM

    OPENROWSET

     

    (BULK 'C:\TEST1.TXT', SINGLE_BLOB)

    AS

     

    R

    SELECT

     

    *,DATALENGTH(DOC)

    FROM

     

    #TESTTAB

     

    Take  a text file keep the size less than 250 , it will insert, when the size is greater than 250 byte it will throw

    Msg 8152, Level 16, State 10, Line 3

    String or binary data would be truncated.

    The statement has been terminated.


    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    • Marked as answer by intertek Tuesday, February 23, 2010 3:35 PM
    Tuesday, February 23, 2010 7:01 AM
  • VARBINARY(250) means a VARBINARY of length 250 bytes, the poster wants to accomodate 256000bytes (250KB).

    From binary and varbinary (Transact-SQL) :
    Use varbinary(max) when the column data entries exceed 8,000 bytes.

    If you want to limit the size of the images being stored then I suspect you may have to do it in your application. This is the correct thing to do anyway, you shouldn't rely on a truncation error as an implementation of business logic.

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    • Marked as answer by intertek Tuesday, February 23, 2010 3:35 PM
    Tuesday, February 23, 2010 9:38 AM
  • Thanks Everyone..

    I appreciate the help!
    Tuesday, February 23, 2010 3:34 PM