Performance overhead of adding a BLOB field to a table

Question

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

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

A colleague's first inclination was to add a new table to hold just the BLOBs, with a (nullable) foreign key in the first table. He predicts this will have performance benefits when querying the first table.

My thoughts were that it is more logical and easier to store the BLOB directly in the original table. None of our queries do SELECT * from that table so my intuition is that storing it directly won't have a significant performance overhead.

I'm going to benchmark both choices but I was hoping some SQL gurus had any advice from experience.

Using MSSQL and Oracle.

Answer 1

For MSSQL, the blobs will be stored on a separate page in the database so they should not affect performance if the column is null.

If you use the IMAGE data type then the data is always stored out of the row. If you use the varbinary(max) data type then if the data is > 8kb it is stored outside the row, otherwise it may be stored in the row depending on the table options.

If you only have a few rows with blobs the performance should not be affected.