FILESTREAM data type in SQL Server 2008

Introduction:
FILESTREAM is a new datatype in SQL SERVER 2008. To use FILESTREAM, a database needs to contain a FILESTREAM filegroup and a table which contains a varbinary(max) column with the FILESTREAM attribute set. This causes the Database Engine to store all data for that column in the file system, but not in the database file. A FILESTREAM filegroup is a special folder that contains file system directories known as data containers. These data containers are the interface between Database Engine storage and file system storage through which files in these data containers are maintained by Database Engine.


Keynotes about FileStream data type:

• FILESTREAM allows large binary data (documents, images, videos, etc.) to be stored directly in the Windows file system. This binary data remains an integral part of the database and maintains transactional consistency. 
• FILESTREAM enables the storage of large binary data, traditionally managed by the database, to be stored outside the database as individual files that can be accessed using an NTFS streaming API. Using the NTFS streaming APIs allows efficient performance of common file operations while providing all of the rich database services, including security and backup.
• So to use FILESTREAM, a database needs to contain a FILESTREAM filegroup and a table which contains a varbinary (max) column with the FILESTREAM attribute set. This causes the Database Engine to store all data for that column in the file system, but not in the database file. 
• A FILESTREAM filegroup is a special folder that contains file system directories known as data containers. These data containers are the interface between Database Engine storage and file system storage through which files in these data containers are maintained by Database Engine.
• A data container is created which will take care of DML statements.
• FILESTREAM will use Windows API for streaming the files so that files can be accessed faster. Also instead of using SQL SERVER cache it will use Windows cache for caching the files accessed.
• When you use FileStream storage, keep the following things in mind:
  • If the table contains a FILESTREAM column, each row must have a unique row id.
  • FILESTREAM filegroups can be on compressed volumes.
  • FILESTREAM data containers cannot be nested.
• When applications need to store large files i.e. larger than 1 MB and also don’t want to affect database performance in reading the data, the use of FILESTREAM will provide a better solution.
• FILESTREAM data is secured by granting permissions at the table or column level.