Access FILESTREAM Data with Transact-SQL

Article
03/08/2017

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

Note

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.

Inserting a Row That Contains 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.

Inserting NULL

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);
GO

Inserting a Zero-Length Record

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)));
GO

Creating a Data File

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)));
GO

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.

Id	SerialNumber	Resume
`C871B90F-D25E-47B3-A560-7CC0CA405DAC`	`1`	`NULL`
`F8F5C314-0559-4927-8FA9-1535EE0BDF50`	`2`	`0x`
`7F680840-B7A4-45D4-8CD5-527C44D35B3F`	`3`	`0x536569736D69632044617461`

Updating FILESTREAM Data

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;

Deleting FILESTREAM Data

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;
GO

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

Note

The underlying files are removed by the FILESTREAM garbage collector.

Share via