CODEWITHSUNDEEP
sql-serverperformanceschemastorage
Liath
Liath

Reputation: 10191

Impacts of Large Nullable Columns in SQL Server

I'm designing a schema where certain members can upload images (based on a permission). I'm planning on doing this using a varbinary(max) column.

What are the storage and performance implications to consider between the two following designs (apart from the obvious that the latter is one to many - that can be constrained easily enough).

Clearly an additional left join will slow performance but if I use a single table approach will this require more storage space (I don't normally consider storage size too much of a concern over performance but for this project I have fairly tight limits with my hosting provider).

Upvotes: 1

Views: 668

Answers (3)

Oleg Dok
Oleg Dok

Reputation: 21766

Store the images in the same table. There will be no any storage or speed benefit of storing them in separate table, except if you'll have zillions of members and 10 of them will have a picture.

Since sql server does not store nullable variable column at all if it has value of NULL - you even may gain speed benefit comparing two-tables design

Consider using FILESTREAM column if your images are big enough (say - more than 1 Mb). It allows to store images as files, which speeding up read-write operations, but with backup consistency.

Upvotes: 1

gbn
gbn

Reputation: 432271

A nullable column variable length that is NULL takes no space in the table.

When you do store the BLOB, then it maybe stored in-row or off-row, depending on size etc. This applies whether 1 or 2 tables

If you have a separate table, you'd additionally need to store keep the primary key of Members (or it has it's own key, FK in Members). However, this is trivial though compared to your picture size.

Personally, I'd use one table to keep it simple.
Unless, say, I wanted to use FILESTREAM, or uses a different filegroup got the BLOBs.

Upvotes: 2

Fedor Hajdu
Fedor Hajdu

Reputation: 4695

Better option... store images on disk and add nullable field with file name (path) in the Members table.

Upvotes: 0

Related Questions