FILESTREAM Feature In SQL Server

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.

1. At the time of installation of SQL server you have to select the option for enabling this FILESTREAM feature
2. 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.

1. Open the SQL Server Configuration Manager from start menu.
   
   
   

2. 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.
   
   
   

3. In property window Go to “FILESTREAM” tab.
   
   
   

4. 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.
   
   
   

5. 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.

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

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

8. Right click on “DATABASES” and select new database.
   
   
   

9. 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.
   
   
   

10. Now GOTO “General” tab. Click on Add button and this will add new row to upper grid.
    
    
11. 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.
    
    
    

12. 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

13. 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.

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.

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#