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:

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:

1_SQL_Server_Implementing_FILESTREAM_attribute_PARTII

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:

3. Storing and Retrieving FILESTREAM data:

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

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

2_SQL_Server_Implementing_FILESTREAM_attribute_PARTII

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

3_SQL_Server_Implementing_FILESTREAM_attribute_PARTII

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

4_SQL_Server_Implementing_FILESTREAM_attribute_PARTII

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:

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.

 

Regards

Piyush Bajaj

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

Follow me on Twitter  |  Follow me on FaceBook