Using the FILESTREAM data type in SQL Server 2008

SQL Server 2008 introduced a FILESTREAM data type to handle images, video and audio.    The data is stored on a windows file share rather than in the database itself.

To enable FILESTREAM, you have to do three steps:

1)   Go into SQL Server Configuration Manager and turn it on.

2)   Create/modify your database to allow FILESTREAM data.   You must have a filegroup that allows for FILESTREAM data.

The following code example creates a database that is named Archive. The database contains three filegroups: PRIMARY, Arch1, and FileStreamGroup1. PRIMARY and Arch1 are regular filegroups that cannot contain FILESTREAM data. FileStreamGroup1 is the FILESTREAM filegroup.

SQL
CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\archlog1.ldf')
GO

For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist. In this example, c:\data must exist. However, the filestream1 subfolder cannot exist when you execute the CREATE DATABASE statement. 

3)   Create a table with a column that is a varbinary(MAX) Filestream data type.    The table with the filestream data will be created in the filestream file group.