FileStream Data in SQL Server 2008
FileStream Data in SQL Server 2008
By : Kasim Wirama, MCDBA
Upcoming SQL Server 2008 (Katmai) introduces Filestream data. With this filestream, you can save file, video or any other file that may have size exceeding 2 GB. Under the hood, they are not really saved in database, but they are still in files, protected by GUID-name folder. This takes advantage of database transaction consistency, while provide native fast access to file through operating system API.
To enable and use these feature you need to make it enable first in master database, then you create database filegroup that contains FILESTREAM, and specify logical and physical name. Physical name here just specify folder where the file will be saved and streamed for later I/O operation. Then you create the table that contains varbinary(max) type with additional FILESTREAM clause, and add one unique identifier column with rowguidcol clause, make sure you give the default value to the uniqueidentifier by NEWID() function. Here is the example :
USE master;
GO
EXEC sp_filestream_configure @enable_level = 3;
Here enable level parameter has value 0,1,2, and 3.
Value 0 means the filestream feature is turned off.
Value 1 means handle only TSQL.
Value 2 handles TSQL and local file only.
Value 3 handles TSQL, local file and remote file operation.
Next the create database with FILESTREAM filegroup below :
CREATE DATABASE myDB ON PRIMARY
(
NAME = myDB_data,
FILENAME = ‘D:\myDB_data.mdf’,
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 20%
),
FILEGROUP filestream_fg CONTAINS FILESTREAM
(
NAME = myDB_fs,
FILENAME = ‘D:\fileData’
) LOG ON
(
NAME = myDB_log,
FILENAME = ‘D:\myDB_log.ldf’,
SIZE= 10MB,
MAXSIZE=UNLIMITED,
FILEGROWTH = 15%
);
GO
Notice that the phrase that I make it bold above. Other thing that you will have default file and folder get created in D:\filedata folder, those file will manage interaction between operating system level and database level.
Then, you can create the table that contains the filestream as below :
USE myDB;
GO
CREATE TABLE movie
(
MovieRowID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL PRIMARY KEY DEFAULT (NEWID()),
MovieName NVARCHAR(100) NOT NULL,
MovieFile VARBINARY(MAX) FILESTREAM
);
GO