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

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

В этой статье описывается, как использовать инструкции Transact-SQL INSERT, UPDATE и DELETE для управления данными 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.

Обновление данных 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.

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

См. также

• Включение и настройка FILESTREAM
• Избегание конфликтов в операциях баз данных в приложениях FILESTREAM