Access FILESTREAM data with Transact-SQL

Artikel
12/15/2022

Applies to: SQL Server

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

Note

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

Insert 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.

Insert NULL

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

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

Insert 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

Create 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 doesn't 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	Chart
`C871B90F-D25E-47B3-A560-7CC0CA405DAC`	`1`	`NULL`
`F8F5C314-0559-4927-8FA9-1535EE0BDF50`	`2`	`0x`
`7F680840-B7A4-45D4-8CD5-527C44D35B3F`	`3`	`0x536569736D69632044617461`

Update FILESTREAM data

You can use Transact-SQL to update the data in the file system file, but you might not want to when streaming 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;

Delete 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 Archive.dbo.Records table, the row is gone, and you can no longer use the associated file.

Note

The underlying files are removed by the FILESTREAM garbage collector.

Check whether a table or database contains FILESTREAM data

To find out whether a database or table contains FILESTREAM data, you must query the system views.

The following extended example shows the steps to create a new database, create tables which have FILESTREAM data, and query system views to see whether the tables, and the database itself, contain FILESTREAM data.

USE [master];
GO

-- Create database with FILESTREAM
CREATE DATABASE [FileStreamTest] CONTAINMENT = NONE ON PRIMARY (
    NAME = N'FileStreamTest'
    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTest.mdf'
    , SIZE = 204800 KB
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 65536 KB
    )
    , FILEGROUP [FileStreamFG] CONTAINS FILESTREAM DEFAULT(NAME = N'FileStreamTestFStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTestFStream', MAXSIZE = UNLIMITED) LOG ON (
    NAME = N'FileStreamTest_log'
    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTest_log.ldf'
    , SIZE = 270336 KB
    , MAXSIZE = 2048 GB
    , FILEGROWTH = 65536 KB
    )
    WITH CATALOG_COLLATION = DATABASE_DEFAULT;
GO

USE [FileStreamTest];
GO

CREATE TABLE FSTiffs (
    Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID()
    , DocumentID INT NOT NULL
    , DocumentType VARCHAR(10) NOT NULL
    , FileContent VARBINARY(MAX) FILESTREAM NOT NULL
    , DateInserted DATETIME
);

-- Which database and files use FILESTREAM 
SELECT db_name(database_id) dbname
    , name AS file_name
    , physical_name
    , type_desc
    , *
FROM sys.master_files
WHERE type_desc = 'FILESTREAM';

-- Which tables in the database have FILESTREAM enabled
USE [FileStreamTest]
GO

SELECT *
FROM sys.tables
WHERE filestream_data_space_id IS NOT NULL;

--insert a TIFF file
INSERT INTO FSTiffs (
    DocumentID
    , DocumentType
    , FileContent
    , DateInserted
    )
SELECT 101
    , '.tiff'
    , *
    , GETDATE()
FROM OPENROWSET(BULK N'C:\Temp\Sample1.tiff', SINGLE_BLOB) rs;

-- Select data from FILESTREAM table
SELECT *
FROM FSTiffs;

-- Update a document
UPDATE FSTiffs
SET FileContent = (
        SELECT *
        FROM OPENROWSET(BULK N'C:\Temp\Sample2.tiff', SINGLE_BLOB) AS rs
        )
WHERE DocumentID = 101;

-- Delete a document
DELETE FSTiffs
WHERE DocumentID = 101;

--clean up any delete files
EXEC sp_filestream_force_garbage_collection @dbname = N'FileStreamTest'
    , @filename = N'FileStreamTestFStream';

Delen via