FILESTREAM Feature In SQL Server

Nirav Daraniya

8y45k7100Article

SQL ServerFILESTREAM Feature

7 Comments

Join the conversation! Your thoughts help the community grow.

samad seif5y ago
Hi i have a stream file table that when I delete a row, that physical file is not deleted. How should that physical file be deleted when deleting the record?
kyi thar7y ago
Thank you so much for this article, Can I use ID column with int and primary key instead of UNIQUEIDENTIFIER ROWGUIDCOL ? Because I want to create Relational DB. Please explain to me. Thanks in advance.
jaz suneer7y ago
Thank u so much for explaining it . I have a doubt . Isn't there uploaded image in that folder with same name and same format?
- Nirav Daraniya7y ago
  Folder structure might be different as per SQL version.
Hadshana Kamalanathan8y ago
Nice explanation, thank you for sharing.
Mohamed Ibrahim8y ago
What is the advantages of the file stream feature? , I created it in my DB but when i take a full back the back up is very very big so I do not prefer this feature
- Nirav Daraniya8y ago
  1).Filestream allows us to store and manage unstructured datain SQL Server more easily. 2).FILESTREAM Columns can be replicated. 3).FILESTREAM enabled databases can be used with LOG Shipping 4).FILESTREAM works with all recovery models 5).FILESTREAM File Groups can be placed on compressed disk volumes 6).The maximum size of the file that can be stored into the FILESTREAM data storage is limited by the size of the disk volume only. Ref. https://www.red-gate.com/simple-talk/sql/learn-sql-server/an-introduction-to-sql-server-filestream/

Today I am explaining what FILESTREAM is and how to use FILESTREAM in SQL server.

FILESTREAM is used for storing documents, images and any other binary objects in SQL database. Before FILESTREAM feature was released in SQL, for storing images in the database we had two options. The first was to store image bytes in varbinary data type and the second was to store image to any location in Windows file system and then store its path in in database table.

Both option have some disadvantages. The first option slows down the system performance and the second option has security issues, because in the second option anyone can see and access your images which are stored in you computer.

From SQL server 2008 FILESTREAM option is available. FILESTREAM is not a data type, it's an attribute of varbinary data type. FILESTREAM allows you to store the large size object in windows file system. Using FILESTREAM you can store any size of object; there is no size limit and you can store files as large as your hard drive size.

FILESTEAM feature is not available in default of SQL server installation. If you want to use FILESTREAM then you have to enable it in SQL server instance.

For enabling the FILESTREAM feature you have 2 options.

At the time of installation of SQL server you have to select the option for enabling this FILESTREAM feature
You can enable it after installation using SQL server configuration manager as shown in the below image.

For enabling FILESTREAM feature after installation in SQL server follow the below steps.

Open the SQL Server Configuration Manager from start menu.

Select SQL services on left panel and then select your SQL server from right panel and right click on it and go to properties menu.

In property window Go to “FILESTREAM” tab.

Enable “FILESTREAM for Transact-SQL access” option and other two options given below and give proper windows share name. Click on Apply and OK button.

Restart the SQL Server service. You feature is now enabled on your SQL server instance and now you have to give access level to them so open SQL server management Studio and execute below command.

EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO

Here “filestream_access_level” has following vvalue.

0 - FILESTREAM feature disable
1 - FILESTREAM feature enable for T-SQL
2 - FILESTREAM feature enable for both

Now our SQL server is enabled with file stream feature.
For using FILESTREAM feature you have to create FILESTREAM enabled database. Follow below process for creating database

Right click on “DATABASES” and select new database.

Go to “FIlegroups” option and click on “Add” button and enter name “FileStreamGP”(you can give any name here) and enable default option as shown in below image.

Now GOTO “General” tab. Click on Add button and this will add new row to upper grid.
Now give any Logical Name: FSTestDB_FilStream and File Type: FileStream Data and Path : give any valid path on your windows file system.

Click on Ok button. This will create new database with FILESTREAM feature.

Now your database is complete with FILESTREAM feature.

For storing blob or binary data in table you have to create table with FILESTREAM attribute. For FIELSTREAM attribute has some limitations.

FIELSTREAM enabled table has one unique row number compalsory.
FIELSTREAM column has “VARBINARY(MAX)” data type.
FILESTREAM enabled database cannot be used for mirroring
FILESTREAM data is not available in database snapshots

Below is a script to create the FILESTREAM enabled data table.

CREATE TABLE TblPhotos(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
Name VARCHAR(20),
Images VARBINARY(MAX) FILESTREAM NULL
)

Script for inserting the records in table is given below.

-- Declare a variable to store the image data
DECLARE @tImage AS VARBINARY(MAX)

-- Load the image data
SELECT @tImage = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK
'C:\Photos\MyPhoto.jpg',
SINGLE_BLOB ) AS x

-- Insert data in table
INSERT INTO TblPhotos (ID, Name, Images)
SELECT NEWID(), 'Image-1',@tImage

In @tImage variable you have to pass valid path of the file (image, video, PDF etc…) please keep in mind that if your image has been stored on another network computer then you have to give network path. Only when image file is stored on computer where SQL server is installed should you give a local drive path otherwise you have to give network path.

After inserting record you can check this record as shown in below image.

SQL server

This image has been stored at below location on windows file system. This is the path which you have given at the time of database creation.

SQL server

For checking if your SQL server instance is enabled with FILESTREAM feature or not you can use below queries.

select * from sys.data_spaces where type='FD';
select * from sys.database_files where type = 2;
SELECT * FROM sys.configurations WHERE name = 'filestream access level'

If you have any questions regarding use of FILESTREAM in SQL server please ask me in comment box.

In the next session I will show you storing and retrieving images, and videos and documents in database using FILESTREAM in WPF C#