Understanding the SQL Server FILESTREM

Introduction

Storing and managing data is always the first priority for a developer. Before MS SQL Server 2008 there are two ways that we fallow to sore and mange the unstructured data.

One approach to store unstructured data is in VARBINARY or INAGE columns. Although it ensures the transactional consistency and reduces data management complexity but performance wise it is very bad in nature.

 Second approach is store the unstructured data as disk files and stores the location of the file in the table along with the other structured data linked to it. The approach is quite good in terms of performance is concern. But it does not always guarantee the transnational consistency as we can remove the physical file without actually deleting the records from table.

MS SQL Server 2008 gives us a new approach to work with FILESTREAM. In this article I am trying to discuss related to it.

What us FILESTREAM

MS SQL Server 2008 introduces the FILESTREAM to work with unstructured data. It allows us to store unstructured data in NTFS file system and mange the transnational constancy with structured data stored in our table object.

How to Install FILESTRAEM Feature

It is a part of MS SQL Server 2008 Installation. By default the FILESTREAM feature is disabling in nature. We have to enable it when installing MS SQL Server 2008.

If we are not enabling the feature of FILESTRAEM we can do it after installation of MS SQL Server 2008.

Step-1

Go to the MS SQL Server 2008 SQL Server configuration Manager and select the Instance of the SQL Server. Then right click it and select property.

Step-2

From Property dialog Select the tab FILESTREAM and click on check box of ENABLE FILESTREAM for Transact - SQL access.

Setting the FILESTREAM Access Level

We have to set the access level of FILESTREAM by using SQL Server Management studio.

EXEC sp_configure filestream_access_level, 2

GO

RECONFIGURE

GO

Using FILESTREAM

To use the FILESTREAM we have to create the FILESTREAM enabled database. The FILESTREAM enabled database contains the flowing storage.

1.    MDF file

2.    LDF file (LOG File)

3.    FILESTREAM Data Container

CREATE DATABASE TEST_DB

ON

PRIMARY

(

   NAME = TESTDB,

   FILENAME = 'E:\SQL Server\TesDB.mdf'

), FILEGROUP TestDBFS CONTAINS FILESTREAM

      (

         NAME = TESTDBFS,

         FILENAME = 'E:\SQL Server\TestDBFS'

      )

LOG ON

   (                       

      NAME = TESTDBLOG,

      FILENAME = 'E:\SQL Server\TesDBLOG.ldf'

   )

GO

Root folder where FILESTREAM data of a database is stored is the FILESTREAM Data Container.

When we create a database with FILESTREAM feature enabled, the FILESTREAM Data Container will be created with the path we specify in your CREATE DATABASE statement.

CREATE TABLE object with FILESTREAM enabled columns

The file stream attribute enabled columns have data type VARBINARY(MAX) data type.

IF OBJECT_ID(N'dbo.tbl_ITEMMASTER', N'U') IS NOT NULL

   BEGIN

     DROP TABLE [dbo].[tbl_ITEMMASTER];

   END

CREATE TABLE [dbo].[tbl_ITEMMASTER]

(

   [ITEMID]    UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

   [ITEMCD]    VARCHAR(20),

   [DESCR]     VARCHAR(50),

   [ITEMIMAGE] VARBINARY(MAX) FILESTREAM NULL

);

GO

It is important that if we use the FILESTREAM there should be one UNIQUEIDENTIFIER columns with ROWGUIDCOL and UNIQUE attributes.

Inserting Data into FILESTREAM

Now we are going to insert some data on our table object.

We have 2 product called Tooth Brush and Tooth Paste. The Images is located on the folder called Item in our local system (C:\Users\aa\Pictures\Item)

-- Declare a variable to store the image data

DECLARE @v_img AS VARBINARY(MAX)

-- Load the image data

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

 FROM OPENROWSET(

 BULK

   'C:\Users\aa\Pictures\Item\ToothBrush.jpg',

   SINGLE_BLOB ) AS x

          

-- Insert the data to the table         

INSERT INTO [dbo].[tbl_ITEMMASTER]

       ([ITEMID], [ITEMCD], [DESCR], [ITEMIMAGE]) 

SELECT NEWID(), 'ITEM101','Tooth Brush', @v_img;

Accessing FILESTREAM data

SELECT * FROM [dbo].[tbl_ITEMMASTER];

ITEMID          ITEMCD    DESCR          ITEMIMAGE

49A0F158-...    ITEM101   Tooth Brush    0xFF..

T-SQL code does not really do any manipulation of the FILESTREAM data except for saving and retrieving it.

It will be the client applications that actually use the FILESTREAM data.

Hope you like it.

Posted by: MR. JOYDEEP DAS