SQL Server 2008 Filestream

This is a long awaited feature! SQL Server 2008 introduced a filestream option that can be applied to a varbinary(max) column, mapping it into the FS!

Here's how:

CREATE DATABASE AdventureWorksRacing ON PRIMARY
( NAME = AdventureWorksRacing_data,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksRacing_data.mdf',
SIZE = 2MB,
MAXSIZE = 50MB,
FILEGROWTH = 15%),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
( NAME = AdventureWorksRacing_media,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksRacing_media')
LOG ON
( NAME = AdventureWorksRacing_log,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksRacing_log.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB);
GO

CREATE TABLE [dbo].[eventMedia] (
[mediaId] [uniqueidentifier] NOT NULL ROWGUIDCOL PRIMARY KEY,
[dateCreated] [datetime] NOT NULL DEFAULT(GETDATE()),
[createdBy] [nvarchar](256) NOT NULL,
[fileName] [nvarchar](256) NOT NULL,
[mediaType] [nvarchar](256) NOT NULL,
[location] [geometry] NULL,
[file] [varbinary](max) FILESTREAM);
GO;

And there you go, upon insertions on this table, the corresponding files will appear on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksRacing_media. It's a kind of magic...


Heard about it at infoq