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.