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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “What is FILESTREAM Data in SQL Server?”

  1. How to use FileStream in LINQ query?

    Suppose we have an input tag of type file. Now, how do I insert the uploaded file in SQL server database using LINQ query?

    And also how to show uploaded file to the user using LINQ query?

Leave a Reply

Your email address will not be published. Required fields are marked *