Managing FILESTREAM Data by Using Transact-SQL
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 How To: Create a FILESTREAM-Enabled Database and How to: 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.