FileStream in SQL Server 2008

In this article, we will see how to work with FileStream in SQL Server 2008 for storing unstructured data like documents and images on file system. Many a times, we always create unstructured data like Images, Documents and Video files. This data is often stored separately outside the database, which depending on your application requirements may increases complexities.

FileStream integrates the SQL Server database engine with an NTFS file system. We can achieve this using VARBINARY (MAX) on the file system. To reduce the effect of the FileStream data on database engine, FileStream uses NT system cache for caching file data and the buffer pool memory is used by the query processor.

If your data is more than 1 MB or if fast access to data is important to your application, then you should use the FileStream object. If the data is less than 1 MB, then it is always better to use VARBINARY (MAX) in the database.

Check if FileStream is Enabled

To start using the FileStream object, you should check whether the FileStream on the instance of SQL Server Database engine is enabled or not. To do so, go to Start > Microsoft SQL Server 2008 > Configuration Tool and click on SQL Server Configuration Manager.

Now right click the instance of SQL Server and go to properties to check whether FileStream is enabled or not. Click on FileStream tab and it should look like below –

Now go to SQL Server Management Studio, and execute the stored procedure shown below to enable or disable the FileStream –

EXEC sp_configure filestream_access_level, 2 RECONFIGURE

Filestream_access_level has three options –

1. 0 – Disable FileStream support for this instance.
2. 1 – Enable FileStream support for T-SQL access.
3. 2 – Enable FileStream support T-SQL and Win32 streaming access.

Now let’s create a database which supports FileStream. Before that, let’s create a folder on ‘C’ drive with the name ‘FSData’. Now run the following query:



CREATE DATABASE CustomerProfiles
ON
PRIMARY ( NAME = CSPKFG,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CustProfile.mdf'),
FILEGROUP CPFileStreamGroup CONTAINS FILESTREAM( NAME = CustDoc,
    FILENAME = 'c:\FSData\custdocs')
LOG ON  ( NAME = custlog,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CustProfile.ldf')
GO

 Now create a table which will store the Customer Profile in document format –



and add the following row to the table –

INSERT INTO CustomerDocuments VALUES (newid (),1,'Pravinkumar', NULL);

When you insert a null value in a FileStream column, the database engine does not create a file on the file system.

INSERT INTO CustomerDocuments
    VALUES (newid (),2,'Yash',CAST ('' as varbinary(max)));

The above insert statement will insert a Zero length record.

INSERT INTO CustomerDocuments
    VALUES (newid (),3,'Yash',CAST ('Sample Data' as varbinary(max)));

The above insert will create a file on the file system. If you now fire a select statement as shown below –

SELECT * FROM CustomerDocuments

The output is shown here–