SQL Server 2008’s new FILESTREAM support combines the benefit of accessing LOBs directly from the NTFS file system with the referential integrity and ease of access offered by the SQL Server relational database engine. To use FILESTREAM support, you must first enable it. Here's how:
1. Enable FILESTREAM Support—Server Instance
You can enable SQL Server 2008 FILESTREAM support as a part of the installation or upgrade process or enable it after the installation has finished. Using T-SQL, run the sp_configure procedure:
EXEC sp_configure FILESTREAM_ access_level, 2; GO RECONFIGURE; GO
where FILESTREAM_access_level tells sp_configure to work with the FILESTREAM feature, and the value of 2 enables FILESTREAM support for T-SQL and Win32 access. A value of 0 disables FILESTREAM support; 1 enables FILESTREAM support for T-SQL access only.
2. Enable FILESTREAM Support—Database
To enable FILESTREAM support in a database, you use the CREATE DATABASE or ALTER DATABASE statement. Below, the ALTER DATABASE statement adds a file group using the CONTAINS FILESTREAM keyword to the Northwind database:
ALTER DATABASE Northwind ADD FILEGROUP FILESTREAMGroup CONTAINS FILESTREAM;
Next, use the ADD FILE keywords to tell SQL Server what NTFS folder to use:
ALTER DATABASE Northwind ADD FILE (
NAME = FSGroupFile,
FILENAME = c:\FSDATA')
TO FILEGROUP FILESTREAMGroup;
3. Use FILESTREAM Data Type in Tables
After you enable FILESTREAM support in the database, the T-SQL code below creates a table containing the FILESTREAM data type. Each row can have one or more FILESTREAM data types, but you must include a column using the UNIQUEIDENTIFIER data type with the ROWGUID attribute.
USE Northwind;
CREATE TABLE FILESTREAMTable (
FSData1 VARBINARY (MAX)
FILESTREAM NULL,
FSGUID UNIQUEIDENTIFIER NOT
NULL ROWGUIDCOL
UNIQUE DEFAULT NEWID ())
4. Access FILESTREAM Data with T-SQL
You can use T-SQL to access FILESTREAM data without having to use ADO.NET. Using T-SQL to insert data, type
INSERT INTO FILESTREAMTable
VALUES(newid(),
CAST ('FSData1' As VARBINARY(MAX)))
