LinkedIn | FaceBook | Twitter
Many shops need to store binary large objects (sometimes called BLOBS) in a database. There are really only two ways to do this: store in them in a table structure in the database itself using a binary data type, or store them in the operating system in a file folder somewhere and point to the file using a text field in a table.
Both of these approaches have issues. Relational Databases aren’t really designed to hold that much data in a single field – not ours, not anybody’s. And pointing to a file is risky, since the file might change, it isn’t under database control for security and backups and so on.
Enter FILESTREAM. In SQL Server 2008 we introduced a feature that actually combines the two approaches into one. Using FILESTREAM, you enter the data into a “column”, but in fact SQL Server stores the data on the hard drive. It’s lightning fast, doesn’t lose the “pointers” to the files, and keeps the whole thing under database control. You can secure it with the same permissions as the database, and it gets picked up in backups and so on.
It does have some limitations and caveats, so be sure and check out the reference data in the following link. And then try it yourself – pretty easy to set up and manage.
Neat. I wonder how this compares featurewise to Oracle's BFILE.
I've been giving some talks about FILESTREAM at Code Camps and SQLSaturday events in the Southeast. I have a full recording of such a session at http://www.adduxis.com/downloads/SqlSat14/Recording/produced.html.
I also have a blog post about using FILESTREAM in an n-tier scenario at http://business.troy.edu/Portal/using-sql-server-2008-filestream-in-an-n-tier-solution.aspx.