SQL Server 2008 Filestream : Why?

simonsabin — Mon, 17 Dec 2007 23:24:43 GMT

Why would you want to use filestream in SQL Server 2008?

The files you store using filestream in SQL Server 2008 are not accessible directly using the normal io methods.Instead you have to make a SQL call to get a pointer which you can then use with normal methods.

So if you have to access SQL anyway why not store the data directly in SQL in the say a varbinary(max) field?

The answer is performance.

Jim Gray and his colleagues did research into the storing of large data in the database (SQL2005) versus the file system (NTFS).

To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem,

In this research it showed that storing anything less than 256Kb was better done in the database, and anything larger than 1M should be stored in the file system. In between depended on the read/write ratio, and storage age.

So if you need to store data blobs that are > 256Kb (per instance) you need to consider filestream.

[18/12/2007 Link fixed]

Simons SQL Blog : Filestream, Katmai

SQL Server 2008 Filestream : Why?