SQL Server FILESTREAM attribute Implementing – PART II

Hi Folks,

In my previous blog, you would have already seen how to enable SQL Server FileStream attributes, or if you want to take a look for that, click here.

In this post, will basically learn how do we actually store and retrieve data using FileStream in SQL Server as per the following steps:

  • Create a FILESTREAM-Enabled database
  • Create a table with FileStream columns
  • Storing and retrieving FileStream data
  • Deleting FileStream Data

So will see all of the above steps one by one:

1. Create a FILESTREAM-Enabled database:

Now after the FILESTREAM has been enabled for machine as well as for server, any database running under those server can have unstructured data, with the help of FileGroup with CONTAINS FILESTREAM clause.

We will create one database like:

NAME = Album_data,
FILENAME = 'C:\SQLAlbum\Album_data.mdf'),
(NAME = Album_group2,
FILENAME = 'C:\SQLAlbum\Photos')
(NAME = Album_log,
FILENAME = 'C:\SQLAlbum\Album_log.ldf')

Here while creating this database; we need to keep few points in our mind:

  • If we are mentioning Primary log while creation of database, we should also mention Secondary log (LOG ON), otherwise it will fail.
  • While keeping the FILENAME for FileStream attributes like here C:\SQLAlbum\Photos, make sure that the pointing directory is not yet created before this, or else creation of database will fail.

So once will create this database with the FileStream location enabled, this is the way the structure of the directory will look like:


2. Create a table with FileStream columns:

We have to keep few points in mind before creating a table for storing FileStream:

  • There should be only one column which has uniqueindentifier column with ROWGUIDCOL attribute where the NULL is not allowed, and also this column should have a unique constraint.
  • For storing BLOB data, any number of FILESTREAM columns in the table can be declared with varbinary(max) datatype.

Now it’s time to create a table:

[PhotoId] int Primary Key,
[Description] varchar(max),
[Photo] varbinary(max) FILESTREAM DEFAULT(0x)

3. Storing and Retrieving FILESTREAM data:

A simple example of how INSERT statement works for this filestream table-

INSERT INTO PhotoTable(photoid, [description], photo) 
VALUES(1, 'caption', cast('cartoon' as varbinary(max)))

Now, if we will do simple select statement, the table will look like-



If we want to see the text inside the varbinary column, then will do a select statement like:


Or else, you can also locate this file inside the folder, and then can see the image from there like:


And also you can edit an image from here, and save it. Like in this case, you can edit it using Notepad.

4. Deleting FileStream Data:

A delete can happen in the same traditional way and so it will also remove the files associated with varbinary(max) from the file system. Eg:

WHERE PhotoId = 1

Well that was a brief description about FILESTREAM attribute in SQL Server.

Hope so I would have cleared it some of your doubts related to FILESTREAM, and please comment if you have some queries related with it.



Piyush Bajaj

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook


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 →

Leave a Reply

Your email address will not be published.