Should blobs be stored in a separate table?

Question

Should blobs be stored in a separate table?

FredS-1001785

Mr or Mrs. 500

Points: 564
More actions
June 1, 2009 at 5:27 am

#210314

I am converting an application from another DB to SQL Server. In a couple cases I will have data about something like an insurance certificate as well as a picture of the certificate.
Is it a better to have the blob be part of the detail record or a separate table?
Fred

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

Very interesting question.

I think if the table is likely to be accessed frequently without retrieving the blob data, such as reporting, listing, that kind of operation - then yes there is an argument for putting the blob in a separate table, especially if you're using a clustered index.

However if you are always retrieving the blob data with the record - then possibly using a single table will give quicker access.

The reasons for my advice are my understanding of the way SQL Server stored data in 8K pages, and the way it allocates more pages, and the way indexes are built. This can be quite a complex topic - and I'm not an expert, so I could be proved wrong.

Also consider the blob density - will every record have data in the blob field, or only a few? - will the table have a clustered index, and what fill factor would you use?

Lowell SSC Guru Points: 323518 More actions · Answer 2

the typical "it depends";

I'd take into consideration whether there is a possibility that the blobs would be one-to-many for the main record. even if "today", you are only capturing one item, is there a chance that might get expanded to multiple images/documents/whatever you are storing? Also,if there is a single exception, where one recordID has two blobs,but the other records all have just one, for me the decision is already made.

If that is the case, I'd go with a separate table.

If you are porting over legacy data, and you will not be gathering the same data in the future, you are pretty much assured the data is 1:1, so adding a column to a table for the blob is OK in my book.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

FredS-1001785 Mr or Mrs. 500 Points: 564 More actions · Answer 3

Thanks for the comments.

As I think about the design there are some situations where I expect multiple documents stored and there are many situations where I would access the base information without the blob.

This pretty well defines the blobs as separate.

I don't know about some of the other issues yet since I am new at this, but I'm sure I will post other questions as I progress.

Fred

Tom257 SSChampion Points: 11543 More actions · Answer 4

and Welcome to the Forum.

Ask anything you want, there's always someone here willing to help 😀