Does changing varbinary(MAX) to varbinary(300) make any difference on the physical disk space?

Firt of all excuse me for the grammar as English is not my primary key :)

I am trying to find out if changing varbinary(max) to varbinary(300) reduces physical disk space usage by the table.

We have a very limited physical disk space and were trying to optimize everywhere including columns in the database.

We have >100 columns (in different tables with millions of rows) with varbinary(max) data type used for storing encrypted values and we don't need the max length as it fits in < 300 length.

Is there any gain in disk space if we switch to varbinary(300) ?
Does varbinary(max) preallocates all its required disk space when creating the table or inserting data into that column?
Does varbinary(max) column take all its disk space even if it has data with length <300?

I haven't been able to find anything anywhere except the following line: "The storage size is the actual length of the data entered + 2 bytes."

https://learn.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql

Aly help in answering the above 3 questions would be appreciated. Thanks.

Solution

Is there any gain in disk space if we switch to varbinary(300) ?

If all your binary values fit into 300 bytes, it will be no change at all in terms of space used. That's because these values are already stored in-row.

The format, in which SQL Server stores the data from the (MAX) columns, such as varchar(max), nvarchar(max), and varbinary(max), depends on the actual data size. SQL Server stores it in-row when possible. When in-row allocation is impossible, and data size is less than or equal to 8,000 bytes, it is stored as row-overflow data. The data that exceeds 8,000 bytes is stored as LOB data

So when you change it to varbinary(300) it will an instantaneous operation with only metadata change.

Does varbinary(max) preallocates all its required disk space when creating the table or inserting data into that column?

No, it doesn't.

Variable-length data types, such as varchar, varbinary, and a few others, use as much storage space as is required to store data, plus two extra bytes

Does varbinary(max) column take all its disk space even if it has data with length <300?

No, as said above, it will take exactly the size need to store the actual value, so if you put a 3-bytes value into varchar(max) column it will used only 5 bytes and if ypu put a null value it will used 2 bytes only (and if the null value is in the last column it will not take space at all)

When you turn varbinary(max) to varbinary(300) as I said it will change nothing data (metadata only), but if you then update the row with a new value, the old column will be dropped and a new one will be created, so not only you'll not gain a space, you'll waste the space, because the space used for old column will not be released, it will be only marked as dropped

Literature: