An Introduction to SQL Server FileStream

Introduction

File Stram is new Feature in sql server 2008 This Feature Used To storage and  management of unstructured data  (example: word documents, image files, music and videos ).

FILESTREAM is not a data type.

Enable FilsStraem

1. Enable  FILESTREAM for Transact-SQL access
2. Enable FILESTREAM for file I/O streaming access
3. Allow remote clients to have streaming access to FILESTREAM data
4. To make this Open SQL Server configraution under sql services select te instance name you want to enable file stream then right click on this instance then select peroperites then filestream tab .
5. After FileStream Enable open sql server query then ruen this script to Configure Filestream

EXEC

sp_configurefilestream_access_level, 2

GO

RECONFIGURE

GO

—————————————————————————

CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = ‘E:\Temp\NP\NorthPoleDB.mdf’
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = ‘E:\Temp\NP\NorthPoleFS’)
LOG ON (
NAME = NorthPoleLOG,
FILENAME = ‘E:\Temp\NP\NorthPoleLOG.ldf’)
GO
—————————————————-
use NorthPole
go
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
———————————————————-
— Declare a variable to store the image data
DECLARE @img AS VARBINARY(MAX)

— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK
‘E:\Temp\NP\MicrosoftMouse.jpg’,
SINGLE_BLOB ) AS x
select @img
— Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), ‘MS1001′,’Microsoft Mouse’, @img
——————————————————————-
–Example No 2
CREATE TABLE dbo.PictureTable
(
PkId int Primary Key IDENTITY (1, 1),
Id uniqueidentifier NOT NULL Unique ROWGUIDCOL Default newid(),
Description nvarchar(64) NOT NULL,
FileSummary varbinary(MAX),
FileData varbinary(MAX) FileStream NULL
)
——————————————————————
Insert Into PictureTable([Description],[FileData])
Values(‘Hello World’, Cast(‘Hello World’ As varbinary(max)))
–And then select using the statement
SELECT [PkId],[Id],[Description],[FileData],CAST([FileData] As varchar(Max)) FROM [PictureTable]
————————————————————————————————-

References

http://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/