FileStream in SQlServer 2008

FileStream in SQlServer 2008

When to Use FILESTREAM:

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

  • Objects that are being stored are, on average, larger than 1 MB.

  • Fast read access is important.

  • You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

FILESTREAM Storage

FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.

To specify that a column should store data on the file system, specify the FILESTREAM attribute on a varbinary(max) column. This causes the Database Engine to store all data for that column on the file system, but not in the database file.

FILESTREAM data must be stored in FILESTREAM filegroups. A FILESTREAM filegroup is a special filegroup that contains file system directories instead of the files themselves. These file system directories are called data containers. Data containers are the interface between Database Engine storage and file system storage. 

When you use FILESTREAM storage, consider the following:

  • When a table contains a FILESTREAM column, each row must have a nonnull unique row ID.

  • FILESTREAM data containers cannot be nested.

  • When you are using failover clustering, the FILESTREAM filegroups must be on shared disk resources.

  • FILESTREAM filegroups can be on compressed volumes.

I hope so, this article will  help you to start with filestream. Thanks for your reading.

Please provide your inputs and suggestions for the post.