FileStream in SQL Server 2008+

One of my friends is working on one project which used to store images and some document files. He is using BLOB fields to get this task done, when he was discussing this with me, I suggested him to use FileStream feature instead of the way he is using currently. There are some reasons for that and I am going to explore it in this article.

In SQL Server 2005, we had two option to deal with binary data (image, document etc.).

1.)    Store images/documents in OS’ file system and keep pointer (path) of that images/documents into database table

2.)    Use BLOB fields to store images/documents directly in database table.

So far we used to take any of the above approaches but both have their own limitations. In first approach, transactional consistency is the issue, when you take backup of database, it won’t get backup of the folders where we are keeping our files (images/documents). In second approach, you can store binary data directly in SQL Server database table so whenever you get database backup, it will backed up by its own but it affects performance while converting that binary data from database table itself, other than that, BLOB field has limitation of 2GB.

To overcome these limitations, Microsoft developer team provided very cool feature, named “Filestream” in SQL Server 2008. With help of “Filestream”, you can store images/documents/videos directly in windows NTFS file system, it has no limitations of 2GB like BLOB and when you take backup of database, your Filestream data will be backed up by its own. Apart from that, you can get advantage of NTFS streaming APIs for efficient and performance driven file operation.

To create Filestream enable column in your table, you have to have a file group in your database which is enable for “FileStream” and you will also need one column in your table which should have varbinary(max) data type so that images/documents/videos could be stored there.

Even before doing any of the above things, you have to enable “Filestream” in your SQL Server which is by default disable.

USE MASTER

GO

--if file stream is not already enable in your server

--look at following link to enable it.

--http://technet.microsoft.com/en-us/library/cc645923.aspx

EXEC sp_configure filestream_access_level, 2

--why I have used 2? know it from below link.

----http://technet.microsoft.com/en-us/library/cc645956.aspx

RECONFIGURE

GO

CREATE DATABASE SQLHubFileStream ON PRIMARY

(

      NAME = SQLHubFileStream_data,

      FILENAME = N'D:\TestDB\SQLHubFileStream_data.mdf'

),

FILEGROUP SQLHubFileStream_FS CONTAINS FILESTREAM

(

      NAME = SQLHubFileStream_FILESTREAM,

      FILENAME = N'D:\TestDB\SQLHubFileStream_FS'

)

 LOG ON

(

      NAME = SQLHubFileStream_LOG,

      FILENAME = N'D:\TestDB\SQLHubFileStream_log.ldf'

);

GO

USE SQLHubFileStream

GO

CREATE TABLE Customers

(

      ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

      Name varchar(25),

      CustPhoto VARBINARY(MAX) FILESTREAM

)

GO

--I have already kept "Ritesh-Teerth.JPG" file in my D drive,

--you have to provide your own path and file name here.

INSERT INTO Customers

VALUES

(NEWID(),'Ritesh Shah',(select * FROM OPENROWSET(BULK 'D:\Ritesh-Teerth.JPG', SINGLE_BLOB) AS img))

GO

select * from Customers

go

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”