-5

What is the Best way to store object almost equal 10MB in SQL Server, storing files maybe will be PDF or Image.

Do you recommend me to use File System(storing path) or database?

Mohamad Mahmoud Darwish
  • 3,865
  • 9
  • 51
  • 76
  • Why you want to store files in database? Can't you can use filestorage and save the path in the database ? If you use DB to store files Serialization and Deserialization will cost you lot of performance – Raghuveer May 05 '16 at 07:30
  • 3
    @irvgk, some possible reasons include simplicity, encapsulation, integrity enforcement, security, reliability. Ofc there are tradeoffs but it may be a valid option depending on priorities, repository size and usage scenarios. This is a valid question. – Imre Pühvel May 05 '16 at 07:55

2 Answers2

3

objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem

You need to decide what to do with objects between 256K and 1M.

Have a read of this Microsoft Research article - which explains this: To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem

Beldi Anouar
  • 2,170
  • 1
  • 12
  • 17
1

Like previous answers have mentioned you should consider if you really need to store binary files in DB in the first place. It may or may not be a good idea. See also this SO question.

Assuming your data set is relatively small and you DO want to give blob storage responsibility to DB then you should consider if you can use FILESTREAM. Note that it rules out DB mirroring, but on upside - it gives great transactional support and integrity over backup/restore scenarios. Something that is quite painful to do properly when storing files in file system directly. Read more about FileStream here.

Assuming you cannot use FileStream or the number of stored files is really small then I'd prefer having a separate blob storage table that contains ONLY the binary + technical PK. That table should never be scanned, just insert/delete/getById. Also, do not use the image type as it is deprecated (link):

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Community
  • 1
  • 1
Imre Pühvel
  • 4,468
  • 1
  • 34
  • 49
  • FileStream option is great which enables administrators to keep transactional data and backup of binary data synchronized. You can save image or PDF data like you save to a file folder and query also using filestream tables: http://www.kodyaz.com/sql-server-2014/filetable-objects-require-filestream-database-option-directory-name-to-be-non-null.aspx – Eralper May 05 '16 at 08:16