How and When to Use SQL Server FILESTREAM Datatypes–Part 2

The FILESTREAM data type is SQL Server’s principle BLOB storage technology. The FILESTREAM data type lets you combine native NTFS file system performance with relational database data integrity. It supports up to 16 TB files which maximum file size supported by the NTFS file systems and simultaneously provides you with full data consistency. The FILESTREAM data type stores pointers in each column that connect the relational data in the row to the BLOB data stored in the file system. This enables those data relationships to be consistent and durable. In Part 1 I covered how to enable the FILESTREAM data type as well as showing how you create databases and tables that use FILESTREAM data. In this article I’ll pick up where that left off and show you how to use the FILESTREAM data type.

Watch a video summary here:

As a quick reminder at the table level the FILESTREAM data type is implemented as a VARBINARY(MAX) column.  The following listing shows a table that uses the FILESTREAM data type. The first column is the required unique nonnull ID. The FSBLOB column uses the VARBINARY(MAX) FILESTREAM data type to indicate that this column will be stored as FILESTREAM data.

CREATE TABLE [dbo].[MyFileStreamTable](

[FSID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

[FSDescription] VARCHAR(50),

[FSBLOB] VARBINARY(MAX) FILESTREAM NULL)

 

Inserting and Querying Character Data

So how do you insert data into this FILESTREAM data type? With the older IMAGE, TEXT, and NTEXT implementations you need special ADO chucking code or the old complex WRITETEXT T-SQL statement to get data into your BLOB data types. That no longer the case with the FILESTREAM data type. You can use the standard T-SQL INSERT statement like you can see below:

INSERT into dbo.MyFileStreamTable VALUES (

newid(), 'BLOB1', CAST ('My first FILESTREAM data' AS VARBINARY(MAX)))

Here you can see that this T-SQL INSERT statement is adding three columns. This first uses the newid() function to generate a unique ID for the FSID column. Next, is a brief text description of the BLOB data. Then you can see that the CAST function is used to add a simple text string into the BLOB column. Since the BLOB column is VARBINARY (MAX) type it can essentially accept any type of data but you need to perform a conversion of the character data to VARBINARY (MAX) to match the required data type.

Next let’s retrieve that data and look at the results. The following code illustrates how you can use the standard T-SQL SELECT statement to query the data that in the FILESTREAM data type.

SELECT FSDescription, CAST(FSBLOB As VARCHAR(20))

FROM dbo.MyFileStreamTable

In this example the SELECT statement is retrieving the FSDescription and FSBLOB columns for the one row that’s currently in the MyFileStreamTable. Here again the CAST function is used to convert the data in the FSBLOB column to character data that can be displayed. You can see the query results in the following listing.

FSDescription

-------------------------------------------------- --------------------

BLOB1                                             My first FILESTREAM

(1 row(s) affected)

Inserting and Querying Binary Data

This first example used a character string to show that using the FILESTREAM from T-SQL is essentially the same model that database administrators and developers are already familiar with. Adding binary data to a table the uses the FILESTREAM data type is similar but there are differences. In the next example you can see how you can use the OPENROWSET command to insert a .jpg into the FILESTREAM data type.

SELECT FSDescription, FSBLOB from dbo.MyFileStreamTable

WHERE FSDescription = 'BLOB2'

 

FSDescription                                     FSBLOB

-------------------------------------------------- --------------------------------

BLOB2                                            0xFFD8FFE000104A4649460001010100 (partial)

(1 row(s) affected)

 

In this example the contents of the FSBLOB column are binary so there’s no need to CAST them. Instead, the SELECT statement will return the binary representation.

In this article you saw how to add and query FILESTREAM data using T-SQL. You can also do this in .NET code to render the FILESTREAM data to your applications. The SqlFileStream object can be used to read and write FILESTREAM data.