1

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).

  • A single table with a nullable varbinary(max) column
  • Two tables, one for Members, the second for Pictures

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).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Liath
  • 9,913
  • 9
  • 51
  • 81
  • sure you want to store images in the db? – vulkanino Feb 09 '12 at 10:55
  • Currently yes to avoid needing write permissions to the hard drive (again not my server so I'm not sure what permissions I'll have) and make any future migration easier – Liath Feb 09 '12 at 10:57
  • I would think twice (or more) before doing so. Anyhow, two tables is the right choice: you may want to add more information to the `user` table later, you would replicate that data for every image he uploads. I wouldn't worry about performace, it's the db job. – vulkanino Feb 09 '12 at 11:02
  • Can you provide a source? You and Oleg have provided conflicting advice and I don't know which is correct – Liath Feb 09 '12 at 11:12
  • 1
    If you store everything in a single table, there's a risk that by doing a `SELECT * FROM dbo.SingleTable` you'll end up getting a lot more data than you want - since that would return all pictures, too. But other than that: having a single table is obviously simpler – marc_s Feb 09 '12 at 11:18

3 Answers3

2

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
1

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.

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • Can you provide a source? You and Vulkanino have provided conflicting advice and I don't know which is correct – Liath Feb 09 '12 at 11:12
  • @Liath Source of what? That NULLABLE column with NULL value does not use space? – Oleg Dok Feb 09 '12 at 11:16
  • @Liath http://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server – Oleg Dok Feb 09 '12 at 11:17
  • @Liath http://social.msdn.microsoft.com/Forums/en/sqldocumentation/thread/0404de89-70dc-4026-9e2e-13ddc3e7c058 – Oleg Dok Feb 09 '12 at 11:18
  • 1
    thanks for that, you answered first so I am giving you the answer. Much appreciated! I will check if I can use the HD on the server as an alternative but your answer was correct for the question asked. – Liath Feb 09 '12 at 11:25
0

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

Fedor Hajdu
  • 4,657
  • 3
  • 32
  • 50
  • Its not better, but available. Since if you want to get consistent backups - you may fail. And FILESTREAM BLOBS are rules here for big files - both performance and consistency – Oleg Dok Feb 09 '12 at 11:09
  • +1 The normal way to store images, for when backup consistency is not a top priority – Andomar Feb 09 '12 at 11:34