storing large files in sql server

Question

What is best practice for storing large photos/text files in sql server. Baring the need for scalability and we are just working with 1 server.

I feel that storing a file path in sql as opposed to a blob is better. Is this true? If we had to scale the software should we still follow this method.

score 11 · Accepted Answer · answered Dec 29 '10 at 20:04

11

It depends on the size of the files.

There is a good Microsoft white paper on the subject, here.

objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors

Of course, their conclusions are specific to SQL Server (2005 and 2008 R2).

answered Dec 29 '10 at 20:04

Oded

489,969
99
883
1,009

Nice paper though, even if it is specific to MS-SQL server! It depends also on the number of images and on the specifications of the application. It is probably safer to BLOB, since it is less error-prone. – Costis Aivalis Dec 29 '10 at 20:12
As SQL Server 2016 supports FILESTREAM, does this answer is true yet? – Mohammad Zatkhahi Jul 25 '22 at 15:59

score 2 · Answer 2 · edited May 23 '17 at 12:05

2

It's a bad idea. Unless you have some very specific reason to store files in data. Already discussed here: Storing Images in DB - Yea or Nay?

If you still insist, read the best practice to do so :) here Best Practices for uploading files to database

edited May 23 '17 at 12:05

Community

1
1

answered Dec 29 '10 at 20:16

Nishant

54,584
13
112
127

1

There's not a week goes by that we don't curse whoever it was that decided to put our images in the DB. I guess it seemed like a good idea at the time and that we'd never have very many images, but it's just not a good idea. – dwidel Dec 29 '10 at 20:26
1

:) especially when your question title says, "storing large files in sql server" By large i meant, larger than one megabyte. But, even if it's smaller, I would not store in DB. Just because, as @David correctly said, it's not the right tool for the job. Files -- I may want to re-size, zip, split or probably want to serve through Amazon S3. I can't just quickly do these, if they are in DB. – Nishant Dec 29 '10 at 20:34

score 1 · Answer 3 · answered Dec 29 '10 at 20:16

It's mostly a question of using the right tool for the job. A lot of time and effort has been put into optimizing a relational database for the purpose of storing relational data. A lot of time and effort has been put into optimizing file systems for the purpose of storing files.

The former can be used to perform part of the job of the latter, but unless there's a really good reason not to use the latter then it's the tool more suited for the job. In nearly every case I've come across, storing the file path (and other relevant information about the file you may want) in the DB and the actual file on the FS is a more well-suited approach to using the tools available.

storing large files in sql server

3 Answers3

Linked