Assigning an empty binary value to a varbinary(MAX) column creates a column 8000 bytes long

Question

I have a table with a varbinary(max) column, i am trying to assign to that column a zero-lengh binary buffer, but instead of getting a zero-length value in the table, i am getting an 8000 bytes long value filled with zeros:

* the dataSize column in the shown query was added using DATALENGHT(data) ("SELECT _index, dataSize=DATALENGHT(data), data FROM....") and shows the actual size on the table of the value

Where does the 8000 bytes long empty buffer come from? is this some kind of default behavior?

Answer 1

If your source column is binary(8000), then DATALENGTH(data) will return 8000 (it is fully padded) and data will contain the full 8000 bytes.

But since you are using

SELECT _index, dataSize=DATALENGTH(data), data FROM

It cannot be a binary(8000) column - because a fixed size column will report the same datalength for all rows. It is likely some data was copied there from a BINARY(8000) variable or other means some time in the past.