The overhead of adding a BLOB field to a table

Question

I am trying to decide between two possible implementations and wish to choose the best one :)

I need to add an optional BLOB field to a table that currently has only 3 simple fields. It is assumed that the new field will be used less than 10%, and maybe even less than 5% of the time, so for most lines it will be zero - in fact, most of our customers will probably never have any BLOB data.

First of all, a colleague had to add a new table to store only BLOBs, with a (zero) foreign key in the first table. He predicts that when querying the first table, this will have performance advantages.

My thoughts were that it was more logical and easier to store BLOBs directly in the source table. None of our queries makes SELECT * from this table, so my intuition is that storing it directly will not have significant overhead.

I am going to compare both options, but I was hoping that some SQL gurus have any advice from experience.

Using MSSQL and Oracle.

Answer 1

For MSSQL, drops will be stored on a separate page in the database so that they do not affect performance if the column is zero.

If you use the IMAGE data type, the data is always stored out of line. If you use the varbinary (max) data type, then if the data is> 8kb, they are stored outside the row, otherwise they can be stored in the row depending on the table parameters.

If you have only a few lines with blocks, performance should not be affected.