VARBINARY(n) vs VARBINARY(MAX) - size

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  November 18, 2017 at 9:52 am

  #402681

  Hello

  I haven't really had the need for VARBINARY before
  Just started filling in some gaps in my SQL knowledge and decided to investigate further

  Here, I appear to have 2 options:

  VARBINARY(n|MAX) where n is between 1 and 8000

  From what I've seen, n is the number of bytes; so a max of 8000
  MAX allows up to 2^31 - 1; approx 2GB

  Is that correct?
  Why the big difference? 8KB vs 2GB

  Thanks

  Damian.

  - Damian

• Jeff Moden

  SSC Guru

  Points: 1004436

  More actions

  November 19, 2017 at 9:58 am

  #1968680

  DamianC - Saturday, November 18, 2017 9:52 AM

  Hello

  I haven't really had the need for VARBINARY before
  Just started filling in some gaps in my SQL knowledge and decided to investigate further

  Here, I appear to have 2 options:

  VARBINARY(n|MAX) where n is between 1 and 8000

  From what I've seen, n is the number of bytes; so a max of 8000
  MAX allows up to 2^31 - 1; approx 2GB

  Is that correct?
  Why the big difference? 8KB vs 2GB

  Thanks

  Damian.

  The big difference is because of 8K pages.  VARBINARY(n) was designed to live on a normal data page.  VARBINARY(MAX) is treated like any other MAX blob.  Typically (especially if longer than 8K), it won't live in a normal 8K page.  Instead, it works like the old IMAGE datatype, which lives in a special kind of storage and only has a pointer to it on normal 8K pages.

  The 2GB limit is likely because of the use of a "double word" behind the scenes, which is 32 bits, which is probably also a leftover from the old FAT32 files which had a limit of 4GB (the total number of values possible in 32 bits) and only the positive side of that range of values was considered for SQL Server.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  November 19, 2017 at 10:27 am

  #1968682

  Jeff Moden - Sunday, November 19, 2017 9:58 AM

  DamianC - Saturday, November 18, 2017 9:52 AM

  Hello

  I haven't really had the need for VARBINARY before
  Just started filling in some gaps in my SQL knowledge and decided to investigate further

  Here, I appear to have 2 options:

  VARBINARY(n|MAX) where n is between 1 and 8000

  From what I've seen, n is the number of bytes; so a max of 8000
  MAX allows up to 2^31 - 1; approx 2GB

  Is that correct?
  Why the big difference? 8KB vs 2GB

  Thanks

  Damian.

  The big difference is because of 8K pages.  VARBINARY(n) was designed to live on a normal data page.  VARBINARY(MAX) is treated like any other MAX blob.  Typically (especially if longer than 8K), it won't live in a normal 8K page.  Instead, it works like the old IMAGE datatype, which lives in a special kind of storage and only has a pointer to it on normal 8K pages.

  The 2GB limit is likely because of the use of a "double word" behind the scenes, which is 32 bits, which is probably also a leftover from the old FAT32 files which had a limit of 4GB (the total number of values possible in 32 bits) and only the positive side of that range of values was considered for SQL Server.

  It's one of those relics, of course a LOB should be bound by storage space and not 2^32 -1 byte size these days.
  😎