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:

CREATE DATABASE Album
ON PRIMARY
(
NAME = Album_data,
FILENAME = 'C:\SQLAlbum\Album_data.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
(NAME = Album_group2,
FILENAME = 'C:\SQLAlbum\Photos')
LOG ON
(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:

CREATE TABLE PhotoTable
(
[PhotoId] int Primary Key,
[RowId] uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
[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:

DELETE FROM PhotoTable
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.

Regards

Piyush Bajaj

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

Follow me on Twitter  |  Follow me on FaceBook