Sample Header Ad - 728x90

Please enlighten me about FILESTREAM and BLOB files

5 votes
2 answers
5184 views
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: 1. 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? 2. 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 run DELETE 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. 3. With both cases, after inserting the .PDF file inside the BLOB VARBINARY(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.
Asked by Racer SQL (7546 rep)
Jul 3, 2018, 03:45 PM
Last activity: Jul 3, 2018, 05:47 PM