Доступ к данным FILESTREAM с помощью Transact-SQL

Статья
06/02/2023

Область применения: SQL Server

В этой статье описывается, как использовать инструкции Transact-SQL INSERT, UPDATE и DELETE для управления данными FILESTREAM.

Примечание.

В примерах этой статьи требуется база данных с поддержкой FILESTREAM и таблица, созданные в базе данных с поддержкой FILESTREAM и создающие таблицу для хранения данных FILESTREAM.

Вставка строки, содержащей данные FILESTREAM

Чтобы добавить строку в таблицу, поддерживающую данные FILESTREAM, используйте инструкцию Transact-SQL INSERT. Значение, вставляемое в столбец FILESTREAM, может быть либо значением NULL, либо значением типа varbinary(max) .

Вставка NULL

Следующий пример иллюстрирует порядок вставки значения NULL. Если значение FILESTREAM равноNULL, ядро СУБД не создает файл в файловой системе.

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

Вставка записи нулевой длины

В следующем примере показано, как использовать инструкцию INSERT для создания записи с нулевой длиной. Это бывает полезно в случае, если нужно получить дескриптор файла, работать с которым предполагается с помощью API-интерфейсов Win32.

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 2, 
      CAST ('' AS VARBINARY(MAX)));
GO

Создание файла данных

В следующем примере кода показывается, как использовать инструкцию INSERT для создания файла, содержащего данные. Ядро СУБД преобразует строку Seismic Data в varbinary(max) значение. FILESTREAM создает файл Windows, если он еще не существует. Затем данные добавляются в файл данных.

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 3, 
      CAST ('Seismic Data' AS VARBINARY(MAX)));
GO

При выборе всех данных в таблице Archive.dbo.Records результат напоминает тот, который приведен в следующей таблице. Однако столбец Id будет содержать разные идентификаторы GUID.

Идентификатор	SerialNumber	Диаграмма
`C871B90F-D25E-47B3-A560-7CC0CA405DAC`	`1`	`NULL`
`F8F5C314-0559-4927-8FA9-1535EE0BDF50`	`2`	`0x`
`7F680840-B7A4-45D4-8CD5-527C44D35B3F`	`3`	`0x536569736D69632044617461`

Обновление данных FILESTREAM

Для обновления данных в файле файловой системы можно использовать Transact-SQL, но при потоковой передаче больших объемов данных в файл может не потребоваться.

В следующем примере любой текст в записи файла заменяется текстом Xray 1.

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' AS VARBINARY(MAX))
WHERE [SerialNumber] = 2;

Удаление данных FILESTREAM

При удалении строки, содержащей поле FILESTREAM, также удаляются и связанные с ней файлы файловой системы. Единственным способом удаления строки и, следовательно, файла является использование инструкции Transact-SQL DELETE.

В следующем примере показано, как удалить строку и связанный с ней файл файловой системы.

DELETE Archive.dbo.Records
WHERE SerialNumber = 1;
GO

При выборе всех данных из Archive.dbo.Records таблицы строка исчезнет, и вы больше не сможете использовать связанный файл.

Примечание.

Базовые файлы удаляются сборщиком мусора FILESTREAM.

Проверьте, содержит ли таблица или базу данных данные FILESTREAM

Чтобы узнать, содержит ли база данных или таблицу данные FILESTREAM, необходимо запросить системные представления.

В следующем расширенном примере показаны шаги по созданию базы данных, созданию таблиц с данными FILESTREAM и представлений системы запросов, чтобы узнать, содержат ли таблицы и саму базу данных данные FILESTREAM.

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

Поделиться через