CODEWITHSUNDEEP
sql
gh9
gh9

Reputation: 10703

storing large files in sql server

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.

Upvotes: 9

Views: 10577

Answers (3)

Nishant
Nishant

Reputation: 55886

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

Upvotes: 2

David
David

Reputation: 219097

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.

Upvotes: 1

Oded
Oded

Reputation: 499382

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

Upvotes: 11

Related Questions