Programming Microsoft® SQL Server® 2012

Storing and Retrieving FILESTREAM Data

To start, you’ll use plain T-SQL to cast simple string data into and out of the varbinary(max) data type of the Photo column. This is certainly contrived, but starting small like this is the best way to learn FILESTREAM. You are going to monitor and observe effects on the NTFS file system as SQL Server utilizes it for BLOB storage. Begin with the following INSERT statement that adds your first row to the PhotoAlbum table:

INSERT INTO PhotoAlbum(PhotoId, Description, Photo)
 VALUES(1, 'Text pic', CAST('BLOB' As varbinary(max)))

This INSERT statement reads no differently than it would if you were using a regular varbinary(max) column for the Photo column without the FILESTREAM attribute. It appears to store the ...