Access FILESTREAM Data with Transact-SQL
This topic describes how to use the Transact-SQL INSERT, UPDATE, and DELETE statements to manage 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 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.
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;
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.
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.