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 NULLUNIQUE,
[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.
