LOB Data Types FILESTREAM Data Type in SQL Server 2008

The FILESTREAM data type combines the performance of accessing LOBs directly from the NTFS file system with the referential integrity and direct access through the SQL Server relational database engine. It can be used for both binary and text data, and it supports files up to the size of the disk volume. The FILESTREAM data type is enabled using a combination of SQL Server and database configuration and the VARBINARY(MAX) data type.

How to Implement FILESTREAM Storage

Enabling SQL Server to use FILESTREAM data is a multiple-step process, which includes:

1. Enabling the SQL Server instance to use FILESTREAM data
2. Enabling a SQL Server database to use FILESTREAM data
3. When creating FILESTREAM-enabled columns in a table, specifying the “VARBINARY(MAX) FILESTREAM” data type.

Enabling the SQL Server instance to use FILESTREAM data

To work with the new FILESTREAM data type you first need to enable it at the server level using sp_configure stored procedure as shown..

EXEC sp_configure filestream_access_level , 2

Note: By default filestream access is disabled.
The sp_configure filestream_access_level stored procedure can enable the access capabilities for T-SQL, the local file system, or remote file system using the following option shown below..

filestream access values & description..

0 - Disabled(default)
1 - T-SQL access
2 - T-SQL and local file system access
3 - T-SQL, local file system, and remote file system access

Enabling a SQL Server database to use FILESTREAM data

The next step is to enable FILESTREAM storage for a particular database. You can do this when you first create a database, or after the fact using ALTER DATABASE. For this example, we will be creating a new database using Transact-SQL.

The Transact-SQL code used to create a FILESTREAM-enabled database looks like this:

CREATE DATABASE FILESTREAM_Database
ON

PRIMARY ( NAME = Data1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FILESTREAM_Database.mdf'),
    FILEGROUP FileStreamGroup CONTAINS FILESTREAM( NAME = FILESTREAM_Data,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FILESTREAM_Data')

LOG ON  ( NAME = Log1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FILESTREAM_Database.ldf')
The above code is similar to the code used to create a regular SQL Server database, except that you can see that there has been the addition of a new filegroup that will be used to store the FILESTREAM data. In addition, when creating the FILESTREAM filegroup, you will be adding the clause “CONTAINS FILESTREAM.”

After the above code runs, and the database is created, a new sub-folder is created with the name of “FILESTREAM_Data.” Notice that this sub-folder name is based on the name I assigned it in the above code.

Inside this newly created folder is a called “filestream.hdr” and an empty sub-folder called $FSLOG. It is very important that you do not delete, modify, or move the“filestream.hdr” file, as it is used to keep track of the FILESTREAM data.

Enabling a column in a table to use FILESTREAM data

Once database is FILESTREAM-enabled, we can start adding new tables that include the VARBINARY(MAX) data type.

The only difference between creating a standard VARBINARY(MAX) column in a table and a FILESTREAM-enabled VARBINARY(MAX) column is to add the keyword FILESTREAM after the VARBINARY(MAX).

Example: