Was this page helpful?
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Access FILESTREAM Data with Transact-SQL


This topic describes how to use the Transact-SQL INSERT, UPDATE, and DELETE statements to manage FILESTREAM data.


The examples in this topic require the FILESTREAM-enabled database and table that are created in Create a FILESTREAM-Enabled Database and Create a Table for Storing FILESTREAM Data.

To add a row to a table that supports FILESTREAM data, use the Transact-SQL INSERT statement. When you insert data into a FILESTREAM column, you can insert NULL or a varbinary(max) value.

The following example shows how to insert NULL. When the FILESTREAM value is NULL, the Database Engine does not create a file in the file system.

INSERT INTO Archive.dbo.Records
    VALUES (newid (), 1, NULL);

The following example shows how to use INSERT to create a zero-length record. This is useful for when you want to obtain a file handle, but will be manipulating the file by using Win32 APIs.

INSERT INTO Archive.dbo.Records
    VALUES (newid (), 2, 
      CAST ('' as varbinary(max)));

The following example shows how to use INSERT to create a file that contains data. The Database Engine converts the string Seismic Data to a varbinary(max) value. FILESTREAM creates the Windows file if it does not already exist.The data is then added to the data file.

INSERT INTO Archive.dbo.Records
    VALUES (newid (), 3, 
      CAST ('Seismic Data' as varbinary(max)));

When you select all data from the Archive.dbo.Records table, the results are similar to the results that are shown in the following table. However, the Id column will contain different GUIDs.













In This Topic

You can use Transact-SQL to update the data in the file system file; although, you might not want to do this when you have to stream large amounts of data to a file.

The following example replaces any text in the file record with the text Xray 1.

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as varbinary(max))
WHERE [SerialNumber] = 2;

In This Topic

When you delete a row that contains a FILESTREAM field, you also delete its underlying file system files. The only way to delete a row, and therefore the file, is to use the Transact-SQL DELETE statement.

The following example shows how to delete a row and its associated file system files.

DELETE Archive.dbo.Records
WHERE SerialNumber = 1;

When you select all data from the dbo.Archive table, the row is gone. You can no longer use the associated file.


The underlying files are removed by the FILESTREAM garbage collector.

In This Topic

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft