locked
max length of varbinary type RRS feed

  • Question

  • Hi,

     

    I am study the varbinary type in SQL Server.

     

    The document at https://msdn.microsoft.com/en-us/library/ms188362.aspx said:

     

    varbinary [ ( n | max) ] : Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes.”

     

    Therefore, the maximum value of n is only 8,000. But when I use “max” as the length parameter, then the maximum length will increase to 2^31 -1 suddently. I cannot understand why SQL Server will use such a design. I think the maximum value of n should equal to the value when using “max” parameter, otherwise, if we know the maximum length is from 8001 to 2^31 - 1, I will have to use “max” as the parameter, which may cause a waste of spaces.

     

    Thanks

    Wednesday, January 6, 2016 2:35 AM

Answers

  • Hi chcw,

    SQL Server used to have a 8060 bytes limitation for each row. From the version 2005 to so far, the new structure using 3 different allocation units can exceeding the limitation.(Table and Index Organization)



    As Dan stated, a row with varbinary(MAX),varbinary(n) and some other type columns can fit into a Data(IN_ROW_DATA) page as long as the row size doesn't exceed the limitation. Subsequently after any operation(like an update statement) that extends the row size over the limitation , the varbinary(MAX) would go to LOB(LOB_DATA) pages, varbinary(n) would go to Row-OverFlow(ROW_OVERFLOW_DATA) pages and pointers would be maintained in the original page.

    Regarding why varbinary(MAX) suddenly increases to 2^31 -1, I think it is just by design, may be for more applicable to meet more demand.

    For your reference.
    SQL Server – Understanding Allocation Units – In Row Data, LOB Data & Row Overflow Data

    If you have any feedback on our support, you can click here.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Wednesday, January 6, 2016 6:57 AM
    • Proposed as answer by Eric__Zhang Thursday, January 21, 2016 10:47 AM
    • Marked as answer by Eric__Zhang Wednesday, January 27, 2016 1:57 AM
    Wednesday, January 6, 2016 6:48 AM

All replies

  • Varbinary(MAX) stores data similarly to varbinary(n) when the row can fit into an 8K page. The length is variable and only the actual size is stored, plus overhead.  However, when the value is over 8000 bytes, the value is stored in separate pages than the rest of the row.  There is more wasted space in that case but it is limited to 8K pages allocations.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, January 6, 2016 3:05 AM
  • The link also says this

    The storage size is the actual length of the data entered + 2 bytes

    So the actual length depends on data you store

    Also for large data ie > 8000 you can choose to store it in DB or in filesystem (using FILESTREAM)

    In DB it supports upto 2 GB and if using FILESTREAM size is limited by your file system for NTFS



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 6, 2016 5:47 AM
  • Hi chcw,

    SQL Server used to have a 8060 bytes limitation for each row. From the version 2005 to so far, the new structure using 3 different allocation units can exceeding the limitation.(Table and Index Organization)



    As Dan stated, a row with varbinary(MAX),varbinary(n) and some other type columns can fit into a Data(IN_ROW_DATA) page as long as the row size doesn't exceed the limitation. Subsequently after any operation(like an update statement) that extends the row size over the limitation , the varbinary(MAX) would go to LOB(LOB_DATA) pages, varbinary(n) would go to Row-OverFlow(ROW_OVERFLOW_DATA) pages and pointers would be maintained in the original page.

    Regarding why varbinary(MAX) suddenly increases to 2^31 -1, I think it is just by design, may be for more applicable to meet more demand.

    For your reference.
    SQL Server – Understanding Allocation Units – In Row Data, LOB Data & Row Overflow Data

    If you have any feedback on our support, you can click here.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Wednesday, January 6, 2016 6:57 AM
    • Proposed as answer by Eric__Zhang Thursday, January 21, 2016 10:47 AM
    • Marked as answer by Eric__Zhang Wednesday, January 27, 2016 1:57 AM
    Wednesday, January 6, 2016 6:48 AM