SQL Server 2008 Creating FILESTREAM Enabled Database

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 PRIMARY
( NAME = N'TestFileStream', FILENAME = N'C:\DB\TestFileStream.mdf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED, FILEGROWTH = 12% )
LOG ON
( NAME = N'TestFileStream_log', FILENAME = N'C:\DB\TestFileStream_log.ldf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED , FILEGROWTH = 12%)
GO

Now the database is ready. Lets go ahead and add the filegroups.

ALTER DATABASE [TestFileStream]
ADD FILEGROUP [TestFileStreamGroup] CONTAINS FILESTREAM
GO

ALTER DATABASE [TestFileStream]
ADD FILE (NAME = N'TestFileStream_FSData', FILENAME = N'D:\DB\TestFileStream')
TO FILEGROUP TestFileStreamGroup
GO

One important fact is the usage of

CONTAINS FILESTREAM

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.

Try this query

Use TestFileStream
GO
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()
)
ON [PRIMARY]
FILESTREAM_ON TestFileStreamGroup
GO

Lets insert some data

Use TestFileStream
GO
INSERT INTO [FileStreamTable] (Data)
SELECT * FROM
OPENROWSET(BULK N'C:\DSCN5021_large.jpg' ,SINGLE_BLOB) AS Document
GO

This will create a folder under “C:\DB\TestFileStream”, if you can travel inside the subfolder and one file will be there. Open it in any image viewer and you can see the image you have inserted.

To retrieve the data, use the following query

USE TestFileStream
GO
SELECT ID
, CAST([Data] AS VARCHAR) as [FileStreamData]
, DataGUID
, [DateTime]
FROM [FileStreamTable]
GO

For updating, use the following query

USE TestFileStream
GO
UPDATE [FileStreamTable]
SET [Data] = (SELECT *
FROM OPENROWSET(
BULK 'C:\DSCN5022_large.JPG',
SINGLE_BLOB) AS Document)
WHERE ID = 1
GO

For deletion, use the following query

USE TestFileStream
GO
DELETE [FileStreamTable]
WHERE ID = 1
GO

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.