This is a really confusing topic for me.
I can understand what a BLOB is, I can easily use it.
I can understand what FILESTREAM is. I can easily implement in on a database, I can backup the database and restore it with the folder created to store these files. Things like these, I think, I can understand well.
What I don't get is:
-
What's the difference between inserting the
.PDF
(for example) using just a VARBINARY(MAX) column in the database, and storing this.PDF
in a FILESTREAM database. I know with the BLOB varbinary(max) the.PDF
is INSIDE the database. If I physically delete the.PDF
, can I still restore it using the value stored inside the varbinary(MAX) column right? -
When I insert this
.PDF
file in a filestream, I can see it inside the folder (the one created for the filegroup), if I insert 100 files, I can see 100 files inside the filestream folder, but when I runDELETE FROM
and delete 100 rows in the database, I can still see these files in the filestream folder. Why does this happen? I thought that, to maintain consistency, these files would be deleted too. -
With both cases, after inserting the
.PDF
file inside the BLOBVARBINARY(MAX)
or FILESTREAM, do I still need the original file? As it is inside the database, I can easily recover it.
I'm using .PDF as an example
I can use and implement both approaches but it still not clear to me what happens with them. For me, it's the same thing, but one is stored outside the database, and the other one inside the database.
Best Answer
Based on the whitepaper written by Paul Randal -
Answering your questions :
Once you insert the pdf into the database as
SINGLE_BLOB
, you dont need the original.pdf
file. You can export out if you need it at later time.File Streams uses a garbage collection process to clean up files that are not longer needed. A system task wakes up periodically (~10 seconds or so) and checks for file stream garbage collection needs. Read "Why don't all the files get collected?"
-
For BLOB Varbinary(max) --> You can delete the original file. For Filestream, do not delete the file from the filestream folder. If the original file is not in the same folder than the filestream folder, then you can delete it.
References :