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.

Answer 1

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

Answer 2

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.