FILESTREAM in SQL Server 2008

How many times have you stored a file in your database? You may have stored it as a blob, which had performance problems. Or you may have had to figure out how to store a link to the file in the database, then figure out how to back up and restore that file.

What you probably wanted is for SQL Server to help you store the file on the file system but provide for backup within SQL Server as part of your normal database backup. No more pointers to files to break.

SQL Server 2008 can now store your file in its own namespace on the local NTFS file system. NTFS also is transactional and supports recovery.The feature is called FILESTREAM.

Roger Doherty and Zach Owens share their experience with FILESTREAM in their blog entry Getting Traction with SQL Server 2008 Filestream. They explain:

SQL Server has always provided the capability to store binary data, and thus you could grab any type of file and stuff it into a SQL Server varbinary(max) column.  But blobs have different usage patterns than relational data, and SQL Server's storage engine is primarily concerned with doing I/O on relational data stored in pages and extents, not streaming blobs.

The blog entry includes an explaination of how to create a FILESTREAM and includes code. It's a great place to get started with this new feature. The example stores video for a WPF application.