What is FILESTREAM Data in SQL Server?

Hello Folks,

FileStream data type has been introduced with the arrival of SQL Server 2008. Before that, we had to use the BOLB data type of SQL Server which allows us to store the data up to 2GB. But one disadvantage with that is file streaming becomes slow and performance of the file stream can be affected very badly.

Well I have made some 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.

Well, this was something about FILESTREAM data type.

Hope you like it 🙂

Please do comment on it 🙂

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook