SQL Server 2012 : Native File Streaming - 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 unstructured Photo column data in line with the rest of the relational columns, and it certainly appears the same way when returning the data back with a SELECT query, as shown here:

SELECT *, CAST(Photo AS varchar) AS PhotoText FROM PhotoAlbum
GO

PhotoId RowId                                Description Photo      PhotoText
------- ------------------------------------ ----------- ---------- ---------
1       FC7D28BC-E8C6-E011-9849-080027565B78 Text pic    0x424C4F42 BLOB

(1 row(s) affected)

However, if you peek inside the FILESTREAM data container, you can verify that SQL Server is actually storing the Photo column outside the database in the file system. Because SQL Server obfuscates the file system that it’s managing for you behind the scenes, you can’t understand the manner in which files and folders are named, organized, and referenced back to the relational database. But just by drilling down and probing the subfolders beneath the Photos directory, you will discover that there is in fact a new file stored in the file system. (Windows will prompt for permission before opening the Photos directory.) This file was created as a result of the INSERT statement. View it by right-clicking the file name and choosing Open, as shown in Figure 1:

Figure 1. Exploring the FILESTREAM file system (note that the actual folder and file names will not match the ones shown in this figure).

If you select Notepad to open the file, you will see clear proof that the unstructured content of the Photo column is stored outside the database and in the file system. In this example, the text BLOB that was inserted into the Photo column is stored in the file that you’ve opened in Notepad, as shown in Figure 2:

Figure 2. Examining unstructured FILESTREAM content in Notepad.

This clearly shows how FILESTREAM data is logically connected to—but physically separated from—the database. Because the unstructured data is stored entirely in the file system, you can easily alter its content by directly updating the file in Notepad without involving the database. To prove the point once again, let’s change the text in the file from BLOB to Cool, and save the changes back to the file system, as displayed in Figure 3.

Figure 3. Changing FILESTREAM content directly in the file system.

The altered FILESTREAM data is reflected in the same SELECT statement you ran earlier, as shown here:

SELECT *, CAST(Photo AS varchar) AS PhotoText FROM PhotoAlbum
GO

PhotoId RowId                                Description Photo      PhotoText
------- ------------------------------------ ----------- ---------- ---------
1       FC7D28BC-E8C6-E011-9849-080027565B78 Text pic    0x436F6F6C Cool

(1 row(s) affected)

You are performing this exercise to verify that SQL Server is using the file system to store varbinary(max) FILESTREAM data. However, we must stress that the purpose here is purely demonstrative. Ordinarily, you must never tamper directly with files in the FILESTREAM data container this way. FILESTREAM provides a total abstraction over the varbinary(max) data type, and you need to consider the physical file system as part of the database (which it is, by virtue of the FILESTREAM group associated with it)—it gets managed by SQL Server exclusively.

Note

Normal SQL Server column-level security permissions apply to varbinary(max) FILESTREAM columns.

In-lining BLOB data in T-SQL is tedious, but it is feasible and can be done when you are working with relatively small byte streams. Run the following INSERT statement to add a second row, this time with a real image in the Photo column.

INSERT INTO PhotoAlbum(PhotoId, Description, Photo)
VALUES(2, 'Document icon', 0x4749463839610C000E00B30000FFFFFFC
6DEC6C0C0C0000080000000D3121200000
000000000000000000000000000000000000000000000000000000021F90
401000002002C000000000C000E0000042C9
0C8398525206B202F1820C80584806D1975A29AF48530870D2CEDC2B1
CBB6332EDE35D9CB27DCA554484204003B)

Now revisit the file system and you’ll find another file was just created. This new file contains the image represented by the byte stream in the INSERT statement you just executed. Right-click the file and choose Open, but this time select Paint to open the file with. Figure 4 shows the image open and magnified in Paint.

Figure 4. Examining unstructured FILESTREAM content in Paint.

More likely, the BLOB source will be an external file. In this case, you can use OPENROWSET with its BULK and SINGLE_BLOB options to consume the file (which can be located on a remote file share) into SQL Server as a varbinary(max) type. For example, if the BLOB is accessible as a file named \\public\shared\doc.ico, then the same result can be achieved with the following INSERT statement:

INSERT INTO PhotoAlbum(PhotoId, Description, Photo)
 VALUES(2, 'Document icon',
  (SELECT BulkColumn FROM OPENROWSET(BULK '\\public\shared\doc.ico', SINGLE_BLOB) AS x))