O'Reilly logo

Programming Microsoft® SQL Server® 2012 by Leonard Lobel and Andrew Brust

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required