In my previous post I talked about the basic idea of what FILESTREAM is. The sales pitch if you will. In this post I thought I would talk about how to actually implement FILESTREAM?. It's actually really easy.
FILESTREAM's live in a filegroup. Semantically, anyway. You will define what folder the data sits in.
ALTER DATABASE Production ADD
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
GO
Simple enough. Next you add a file to the filegroup.
ALTER DATABASE Production ADD FILE (
NAME = FSGroup1File,
FILENAME = 'F:\Production\FSDATA')
TO FILEGROUP FileStreamGroup1;
GO
Again, pretty simple. You'll notice the F:\Production\FSDATA. That’s where the files are stored. Now, lets create a table that holds the FILESTREAM.
CREATE TABLE DocumentStore (
DocumentID INT IDENTITY PRIMARY KEY,
Document VARBINARY (MAX) FILESTREAM NULL,
DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
UNIQUE DEFAULT NEWID ())
FILESTREAM_ON FileStreamGroup1;
GO
And that's it. To insert data into the table, do it like you would any VARBINARY data.