In this post I am going to explain how you can create a database with the new FILESTREAM feature enabled. If you want to know, how to enable FILESTREAM in instance level, please refer to my previous post "SQL Server 2008 FILESTREAM Feature".
Lets start by creating a database name TestFileStream using the below scripts.
CREATE DATABASE [TestFileStream] ON PRIMARYNow the database is ready. Lets go ahead and add the filegroups.
( NAME = N'TestFileStream', FILENAME = N'C:\DB\TestFileStream.mdf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED, FILEGROWTH = 12% )
( NAME = N'TestFileStream_log', FILENAME = N'C:\DB\TestFileStream_log.ldf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED , FILEGROWTH = 12%)
ALTER DATABASE [TestFileStream]One important fact is the usage of
ADD FILEGROUP [TestFileStreamGroup] CONTAINS FILESTREAM
ALTER DATABASE [TestFileStream]
ADD FILE (NAME = N'TestFileStream_FSData', FILENAME = N'D:\DB\TestFileStream')
TO FILEGROUP TestFileStreamGroup
clause. Atleast for one filegroup we must specify this clause. Open the properties window of the newly created database, and look into the Files section. There you can see that for the file “TestFileStream_FSData”, the file type is “File Stream Data”. Now open the folder “C:\DB”. There will be folder named “TestFileStreamData”. All the FILESTREAM related data gets stored in TestFileStreamData folder which is also known as FILESTREAM Data Container. Inside this folder you can see the following files
Among this, the file “filestream.hdr” is the most important one. As the name suggests it hold the file stream information.
Let go ahead and create a table. Before creating keep a note of the following points.
- Must have a column of type VARBINARY(MAX) along with the FILESTREAM attribute.
- Table must have a UNIQUEIDENTIFIER column along with the ROWGUIDCOL attribute.
CREATE TABLE [FileStreamTable]
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Data] VARBINARY(MAX) FILESTREAM NULL,
[DataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
[DateTime] DATETIME DEFAULT GETDATE()
INSERT INTO [FileStreamTable] (Data)
SELECT * FROM
OPENROWSET(BULK N'C:\DSCN5021_large.jpg' ,SINGLE_BLOB) AS Document
To retrieve the data, use the following query
, CAST([Data] AS VARCHAR) as [FileStreamData]
SET [Data] = (SELECT *
SINGLE_BLOB) AS Document)
WHERE ID = 1
For deletion, use the following query
WHERE ID = 1
On updating/deleting, the table will be updated/deleted immediately. But the FileStream container data will be removed once the Garbage Collector Process runs.
That’s all for this post. In my next post I am planning to explain optimizing FileStream objects.