Filestream in SQL Server

What is Filestream?

A traditional database is used to store regular table-based data. With Filestream, users can store unstructured data, such as documents, images, videos, audio, etc., in SQL server applications. There is three optimal way to store this kind of data in a database;

• Keeping the path of the data inside the database
• Using Binary large objects(BLOB)
• Filestream

Unlike BLOB, Filestream has no storage limit of up to 2 GB. Storing up to 2 GB with BLOB slows down the performance of the database. With the increase of the data inside the database, this slowness can cause problems. Filestream uses NTFS streaming API, which helps to increase performance while storing and querying the data.

If the user stores the data as BLOB inside the database, it is stored as varbinary(max). With Filestream, data is stored as varbinary(max) Filestream. If it is defined like this, it will store the data both as binary and as a file inside the desired directory that has been defined.

When to use Filestream?

As described in the previous section, users can store unstructured data as BLOBs or Filestreams. Based on the conditions of the application that has been developed, the user may need to choose between those two. Filestream should be considered when the following points are important:

• If the data can surpass the 2 GB limit of BLOB
• If the stored data is larger than 1 MB
• Querying is important

Why does Filestream store data both on the database and as a file on the file system?

Filestream stores data in the database and on the file system for data management, performance and flexibility. This approach uses the strengths of both database and file systems.

• Performance: Storing extensive binary data in the database can cause performance issues if the stored data is relatively large. Storing this data in the file system it can efficiently manage these files and access them with file I/O operations.
• Consistency: SQL Server helps Filestream data remain consistent with the database.
• Integration: It is integrated with the database engine. With this integration, managing and querying stays relatively simple.
• Security: Storing the metadata in the database provides protection.

How to use Filestream?

To use it, the user has to specify the Filestream property in the column she/he wants to use it. This attribute causes the Database Engine to store all data for that column in the file system and not in the database file.

Get Kamil Uğur Sarp’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

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

• First, We need to activate the Filestream property inside the SQL Server Configuration Manager.
• Right-click on the SQL Server

• And then switch to the FILESTREAM tab. We must tick ‘Enable FILESTREAM for Transact-SQL access’ and ‘Enable FILESTREAM for file I/O access’. With these options enabled, we can use Filestream and read/write operations.
• After applying the changes, we have to restart the SQL Server and complete the changes inside SQL Server Configuration Manager.

• Under SQL Server Management Studio, right-click on the database we want to enable the Filestream and select properties.

• Under Filegroups, the user has to create a new Filestream. This will be the filestream property that we are going to use. Click on ‘Add Filegroup’ and create a new Filegroup.

• Under the Files section, we add a new Database File. We need to give it a proper name and file type. Under Filegroup, we choose the Filegroup we created in the previous step. Lastly, we are going to select a path to store the data. After all these steps are done, we can create a table using Filestream.

• We are permitting the use of Filestream in our database.

ALTER DATABASE FileStreamDatabase
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileStreamDirectory');

• Our database is ready for use with varbinary (MAX) Filestream after specifying which column value will be Filestream.

CREATE TABLE ExampleTable
(
    ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    Name NVARCHAR(255),
    DataFile VARBINARY(MAX) FILESTREAM,
    CONSTRAINT [PK_FileStorage] PRIMARY KEY CLUSTERED (ID)
) FILESTREAM_ON FileStream;

Thank you for taking the time to read this article. If you have any questions about the article or need help, you can reach me via Medium or Linkedin.