SQL Server A to Z – FILESTREAM

Data doesn’t always come in a nice, neat, relational format.  Oftentimes it’s unstructured files like Word documents or images.  But it still needs to be stored in some manner, right?  In previous versions of SQL Server, you could store this kind of data in the database using varbinary or image columns.  Another alternative was keep the unstructured files outside the data and just store the location of the files in the database.  Each of these options has its shortcomings, be it performance, data consistency, code complexity or basic database maintenance.

Filestream

With SQL 2008 came a new feature called FILESTREAM.  FILESTREAM allows us to store this unstructured data in an NTFS filesystem while still treating it like part of the database.  You can use basic T-Sql statements like SELECT, INSERT, and UPDATE against FILESTREAM data.  And you can back up FILESTREAM data as part of the database.  Pretty neat, huh?

Enabling FILESTREAM 

FILESTREAM can be enabled as part of the SQL Server installation.  However, this is not the default, so unless you specifically configured it at install-time, it’s probably not enabled on your server.  Don’t worry, though, FILESTREAM can be enabled relatively easily after installation. Open up SQL Server Configuration Manager, right-click on the SQL Server Service and select Properties.  Click on the FILESTREAM tab.  Select the appropriate checkboxes and provide a Share Name as shown below.  Click OK.

Next, in SSMS, right-click on the instance name and select Properties.  Go to Advanced, and click the drop down menu for Filestream Access Level.  Choose Transact-Sql Access Enabled or Full Access Enabled, and click OK.

FILESTREAM is now enabled for your SQL instance.

Using FILESTREAM

The first step in actually using FILESTREAM data is to create a FILESTREAM-enabled database.  This is really just your typical CREATE DATABASE statement, with an additional filegroup. This will tell SQL Server where to store the unstructured data. SQL Server will create the folder specified, so make sure it doesn’t already exist. Using my example below, ensure the “filestream1” folder doesn’t already exist in “D:data”. If it does, you’ll get an error.

CREATE DATABASE MyFSDatabase
ON
PRIMARY ( NAME = MyFSData1, FILENAME = 'D:dataFSData1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = MyFSData2, FILENAME = 'D:datafilestream1')
LOG ON  ( NAME = MyFSLog1,  FILENAME = 'D:logsFSLog1.ldf')
GO

Now we’re ready to create a table containing FILESTREAM data.

USE MyFSDatabase
GO
CREATE TABLE MyPics (
ID			UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
Name		varchar(50),
Picture		VARBINARY(MAX) FILESTREAM NULL);
GO

And we can insert data using simple INSERT statements.

INSERT INTO MyPics
SELECT	NEWID(),
		'Penguins',
		CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK 'D:Penguins.jpg',SINGLE_BLOB ) AS x

You can’t really view FILESTREAM data via T-Sql. If I try to SELECT the row I just inserted the Picture column will just be a hex string. So you’d need an application to handle retrieving and viewing the data. But that’s the developer’s job, right? 😉

Also recommended: