SQL Server 2008 Streaming

it's new feature in sql server 2008. in the installation steps you can support you server to working with stream .

before working with database stream, goto configuration manager and select properties of your instance that you want to act with streaming. 



 then select filestream tab, and click on you need from this tab .
  

then open new query in sql server and write the following code to full access enabed file stream on server level .

-- filestream configuration

-- The last parameter to sp_configure specifies the Access Level,
-- where 0 means ‘Disabled’,
-- 1 means ‘Transact-SQL Access Enabled’ and
-- 2 means ‘Full Access Enabled’
EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO

Now, we can create database with streaming option by following code.

CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = 'C:\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = 'C:\NorthPoleFS')
LOG ON (
NAME = NorthPoleLOG,
FILENAME = 'C:\NorthPoleLOG.ldf')
GO



return to database to see the effective of above code .

Return to your query again, and past the following code.

CREATE TABLE [dbo].[Items](

[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)

the above code is about create table Items, note the column ItemImage it's varbinary(max) datatype and also filestream. filestrem keyword just attribute behinde varbinary datatype , it's note a datdatype. filestrem keyword found here for working with streaming database.
ItemID column in previous code with datatype UNIQUEIDENTIFIER, for working with streaming in your datdabase add attribute ROWGUIDCOL. this attribute keep UNIQUEIDENTIFIER with sequential result as you know with normal identity column result.
the final step now is to insert binary data to the previous table .
-- 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 'C:\img1.jpg',
SINGLE_BLOB ) AS x
-- Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), 'MS1001','Microsoft Mouse', @img

the previous code try to insert image file named 'img1.jpg' to table items.
 
but what about streaming, the above code can be run withtout any previous configuration . it's mean that the streaming database not mean inserting data to database, but it's mean how to read data binary from database by synchronise manner.
 
all previous configuration let you working with streaming database, to read your data by stream manner you can do that by programming language such as vb.net or c#.