SQL 2008: Filestream versus Binaries in the database.

Question

Hello,

Yesterday I asked the question on how I should save my files. After some research I've desided to go with storing the files "in" the database.

I've checked the difference between storing the files using filestream and storing the files in the database itself.

Each has it's advantages and disadvantages. To help me with my research this site helped me out a lot: http://www.codeproject.com/KB/database/SqlFileStream.aspx

So basically it says that saving the files using filestream is better if the files are bigger than 1mb.

But I've discovered another problem with filestreaming. If you delete a record in the database the file still exists on the filesystem.

Therefore I need you guys opinion. What to use? Filestream or saving the files in the database using VARBINARY?

Grtz, M.

Answer 1

Use Filestreams.

Databases are designed to store relational data, file systems are designed to store files.

I heard often from Microsoft employees that storing large blobs in an SQL Server 2000/2005 (cant remember) is not a good idea.

Also consider Backup: A Databasefile is ONE file (EDIT: If you configure it so). If you're using Filestreams you can backup individual files.

Answer 2

The data on the filesystem will (should) be removed soon after you delete the data from the database, though this is done in a separate system background thread, so it may remain on the filesystem until basically the garbage collector runs again. However, all results accessed through any of the filestream APIs (i.e. tsql or streaming) will be guaranteed to not access anything that has been removed, whether or not the filesystem data still resides on disk (ACID is ensured with filestream).