SQL Server - FileStream

FILESTREAM

Filestream allows us to store and manage unstructured data in SQL Server more easily. FILESTREAM feature is available in SQL Server 2008 and above releases.

Steps to configure and run statements using SQL Server FileStream:

Enable Filestream (Step – 1)

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

GO

Create a FILESTREAM-enabled database (Step – 2)

CREATE DATABASE Sample_FS ON PRIMARY

      (

            NAME = SamFS1,

            FILENAME = 'E:\Data\samfs1.mdf'),

            FILEGROUP FSGroup1

      CONTAINS FILESTREAM

            (

                  NAME = SamFS2,

                  FILENAME ='E:\Data\Filestream1'

            )

      LOG ON

            (

                  NAME = SamFSlog1,

                  FILENAME ='E:\Data\samfslog1.ldf'

            )

GO

Create a Table for Storing FILESTREAM Data (Step – 3)

CREATE TABLE Sample_FS.dbo.Example

(

[ExampleID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL UNIQUE,

[SNo] INT UNIQUE,

[FS_Column] VARBINARY(MAX) FILESTREAM NULL

)

GO

Inserting a Row That Contains FILESTREAM Data (Step – 4)

-- Inserting NULL

INSERT INTO Sample_FS.dbo.Example VALUES (newid(), 1001, NULL);

-- Inserting a Zero-Length Record

INSERT INTO Sample_FS.dbo.Example VALUES (newid(), 2001, CAST('' AS VARBINARY(MAX)));

-- Creating a Data File

INSERT INTO Sample_FS.dbo.Example VALUES (newid(), 3001, CAST('This will be viewed using NOTEPAD from the FileStreal location.' AS VARBINARY(MAX)));

-- Creating a Data File using existing .jpg file

DECLARE @p_img AS VARBINARY(MAX);

SELECT @p_img = CAST(bulkcolumn AS VARBINARY(MAX))

FROM OPENROWSET(BULK 'D:\error.jpg', SINGLE_BLOB ) AS x;

INSERT INTO Sample_FS.dbo.Example ([ExampleID], [SNo], [FS_Column])

SELECT NEWID(), 4001, @p_img;

Update FILESTREAM Data (Step – 5) 

UPDATE Sample_FS.dbo.Example

SET    [FS_Column] = Cast('This is the update' AS VARBINARY(MAX))

WHERE  [SNo] = 2001;

Deleting FILESTREAM Data (Step – 6) 

DELETE FROM Sample_FS.dbo.Example

WHERE  [SNo] = 1001;

Useful Instructions

• Make sure E drive must have a “DATA” named folder. (see Step-2)
• Make sure D drive must have a “error.jpg” file. (see step-4)
• You can use the SELECT statement on the table “Example” but the data of column where “FILESTREAM” is used will be in hexa.
• You can open the files which are stored using FILESTREAM after it is deleted from its original location or source location i.e. in step-4 “error.jpg” file is on D drive after successful execution of the insert statement, the same file can be removed from D drive.
• You can get/see the same “error.jpg” from the location “E:\Data\Filestream1\_____”.