FILESTREAM in Sql Server 2008 and Example

FILESTREAM in SQL Server 2008

SQL Server 2008 introduces the new FILESTREAM attribute, which can be applied to the varbinary(max) data type. Using FILESTREAM, you can exceed the 2GB limit on stored values and take advantage of relational handling of files via SQL Server, while actually storing the files on the file system. BACKUP and RESTORE operations maintain both the database data as well as the files saved on the file system, thus handling end-to-end data recoverability for applications that store both structured and unstructured data. FILESTREAM marries the transactional consistency capabilities of SQL Server with the performance advantages of NT file system streaming.

SQL Server can manage the contents of the FILESTREAM containers on the file system for you and control access to the files, while the NT File System (NTFS) provides efficient file streaming and file system transaction support. This combination of SQL Server and NTFS functionality provides several advantages when dealing with LOB data, including increased efficiency, manageability, and concurrency.

Check whether the filestream property is enabled or not. To do this executes the following query.

SELECT SERVERPROPERTY('FilestreamShareName') ShareName,
SERVERPROPERTY('FilestreamEffectiveLevel') EffectiveLevel,
SERVERPROPERTY('FilestreamConfiguredLevel') ConfiguredLevel

If it is not enable use sp_configure procedure to enable it.

EXEC sp_ configure 'filestream access level', 2;
GO
RECONFIGURE;
GO

FILESTREAM Access Levels:


0. Disable

1. Access via T-SQL only

2. Access via T-SQL only and file system

Once you’ve enabled FILESTREAM support on your SQL Server instance, you have to create a SQL Server file group with the CONTAINS FILESTREAM option. This file group is where SQL Server will store FILESTREAM BLOB files.

Example:

CREATE DATABASE Jobin ON PRIMARY
(
NAME='Jobin_Data',
FILENAME='E:\Jobin\sql\jobin.mdf'
),
FILEGROUP DocumentFileStreamGroup CONTAINS FILESTREAM
(
NAME='FileStreamdocuments',
FILENAME='E:\Jobin\sql\jobin_documents'
)
LOG ON
(
NAME='Jobin_Log',
FILENAME='E:\Jobin\sql\jobin.ldf'
);

To enable FileStream to an existing database execute the following query

-- to add filestream to an existing database
ALTER DATABASE Jobin
ADD FILEGROUP DocumentFileStreamGroup CONTAINS FILESTREAM;
GO
ALTER DATABASE Jobin
ADD FILE
(
NAME = 'FileStreamdocuments'
FILENAME = 'E:\Jobin\sql\jobin_documents'
)
TO FILEGROUP DocumentFileStreamGroup;

FILESTREAM Enabled Tables


Once you’ve enabled FILESTREAM on the server instance and created a FILESTREAM filegroup, you’re ready to create FILESTREAM-enabled tables. FILESTREAM storage is accessed by creating a varbinary (max) column in a table with the FILESTREAM attribute. The FILESTREAM-enabled table must also have a uniqueidentifier column with a ROWGUIDCOL attribute.

CREATE TABLE Employee
(
EmpId UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY,
Name VARCHAR(50),
Document VARBINARY(MAX) FILESTREAM
)

Insert some records into the table.

INSERT INTO Employee VALUES(NEWID(),'Jobin',CAST('jobin john' AS VARBINARY(MAX)));
INSERT INTO Employee VALUES(NEWID(),'Ajith',CAST('Software Engineer' AS VARBINARY(MAX)));

Select the inserted values

SELECT * FROM Employee
SELECT EmpId,Name,CAST(Document AS VARCHAR) AS Document FROM Employee