FileStream Data Type

Filestream data type used to store data(unstructured data) as varbinary(MAX) in separate file in the file system that means not with database file. This increase the performance of read and writes to database of this type.

A VARBINARY (MAX) column with FILESTREAM attribute is not restricted to the 2 GB limit SQL Server imposes on Large Value Types. The size of the file is limited by the size of the disk volume only.

Why FileStream data type?

If you think to separate your files storing from its structured storage, This separation can cause data management complexities and if the data is associated with structured storage, the file streaming capabilities and performance can be limited.

Note:Your data must be large than 1 mb in size to involve the filestream data type.

To implement Filestream in your database do the following:

1. Enblae Filestream in Sql server 2008
2. Create filegourp that contains the filestream database file

You can enable Filestream in Sql server in three ways:

1. At the time of installation SQL Server 2008
2. or by using T-SQL (sp_configure)
3.  Or by SQL Server Configuration manager

Let's see the easiest way which is by Sql Server Configuration manager:

So first open SQL Server Configuration manager and then right click on your instance name and choose properties and select Filestream tap and enable it.

Let's see an example:

1. After you enabled the Filestream in your instance
2.  Create a folder called "TestDb" in C: partition
3. Then run this command to create a database with three fille (.mdf,.ldf,filestream data container or folder)

CREATE DATABASE TestDb 
ON
PRIMARY ( 

    NAME = TestDB, 
    FILENAME = 'C:\TestDb\TestDb.mdf'

), 
FILEGROUP TestDbFS CONTAINS FILESTREAM( 

     NAME = TestDbFS,
     FILENAME = 'C:\TestDb\TestDbFS')

LOG ON (                         

      NAME = TestDbLog,
      FILENAME = 'C:\TestDb\TestDbLog.ldf')
GO

4. Open c:\TestDb in windows explorer and you see the following:


You will see a folder “$FSLOG” and a file “filestream.hdr”.
The folder “$FSLOG” is the FILESTREAM equivalent of the Database Transaction Log file. “filestream.hdr” contains important metadata information used by SQL Server internally.

5. Create a table has a filestream attribute

USE TestDb
GO

CREATE TABLE [dbo].[Employees](
    -- Table which use filestream must has 
    -- UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE column
   Id        UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   FullName NVARCHAR(50),
   EmpPhoto VARBINARY(MAX) FILESTREAM NULL

)
GO

6. Now let's insert a record to the above table

USE TestDb
GO
--This code to load the image from the disk to
-- @image variable
DECLARE @image AS VARBINARY(MAX)
SELECT @image = CAST(bulkcolumn AS VARBINARY(MAX))

      FROM OPENROWSET(
      BULK
      'C:\TestDb\1.png',
      SINGLE_BLOB ) AS a

-- Then inserted to a table          
INSERT INTO Employees (Id, FullName, EmpPhoto)
SELECT NEWID(), 'Fadi Ahmad',@image
GO

7. And try to run this command

USE TestDb
GO

SELECT *, EmpPhoto.PathName() FROM Employees
GO

You will see that the image data displayed as binary data.