Zugriff auf FILESTREAM-Daten mit Transact-SQL

Artikel
07/12/2024

Gilt für: SQL Server

Dieser Artikel beschreibt, wie Sie die Transact-SQL-Anweisungen EINFÜGEN, UPDATE und LÖSCHEN zur Verwaltung von FILESTREAM-Daten verwenden.

Hinweis

Für die Beispiele in diesem Artikel sind die FILESTREAM-aktivierte Datenbank und die Tabelle erforderlich, die unter Erstellen einer FILESTREAM-aktivierten Datenbank und Erstellen einer Tabelle zum Speichern von FILESTREAM-Datenerstellt werden.

Einfügen einer Zeile mit FILESTREAM-Daten

Zum Hinzufügen einer Zeile in einer Tabelle, die FILESTREAM-Daten unterstützt, verwenden Sie die Transact-SQL EINFÜGEN-Anweisung INSERT. Wenn Sie Daten in eine FILESTREAM-Spalte einfügen, können Sie NULL oder einen varbinary(max) -Wert einfügen.

NULL einfügen

Im folgenden Beispiel wird gezeigt, wie NULLeingefügt wird. Wenn der FILESTREAM-Wert NULL ist, erstellt Datenbank-Engine keine Datei im Dateisystem.

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

Einfügen eines Datensatzes mit der Länge Null

Das folgende Beispiel veranschaulicht, wie mit INSERT ein Datensatz mit der Länge 0 (null) erstellt wird. Dies ist hilfreich, wenn Sie ein Dateihandle abrufen müssen, die Datei jedoch durch die Verwendung von Win32-APIs geändert wird.

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

Erstellen der Datendatei

Das folgende Beispiel veranschaulicht, wie mit INSERT eine Datei mit Daten erstellt wird. Die Datenbank-Engine konvertiert die Zeichenfolge Seismic Data in einen varbinary(max)-Wert. FILESTREAM erstellt die Windows-Datei, wenn sie noch nicht vorhanden ist. Die Daten werden dann der Datendatei hinzugefügt.

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

Wenn Sie alle Daten in der Tabelle Archive.dbo.Records auswählen, sollten ähnliche Ergebnisse wie in der folgenden Tabelle angezeigt werden. Die Id -Spalte enthält jedoch andere GUIDs.

Kennung	SerialNumber	Diagramm
`C871B90F-D25E-47B3-A560-7CC0CA405DAC`	`1`	`NULL`
`F8F5C314-0559-4927-8FA9-1535EE0BDF50`	`2`	`0x`
`7F680840-B7A4-45D4-8CD5-527C44D35B3F`	`3`	`0x536569736D69632044617461`

Aktualisieren von FILESTREAM-Daten

Sie können Transact-SQL zum Aktualisieren der Daten in der Dateisystemdatei verwenden. Dies ist jedoch nicht empfehlenswert, wenn Sie große Datenmengen in einer Datei streamen müssen.

Im folgenden Beispiel wird der gesamte Text im Dateidatensatz durch den Text Xray 1ersetzt.

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

Löschen von FILESTREAM-Daten

Wenn Sie eine Zeile mit einem FILESTREAM-Feld löschen, löschen Sie auch die zugrunde liegenden Dateisystemdateien. Die einzige Möglichkeit zum Löschen einer Zeile, und somit der Datei, ist die Transact-SQL LÖSCHEN-Anweisung.

Im folgenden Beispiel wird das Löschen einer Zeile und der entsprechenden Dateisystemdateien veranschaulicht.

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

Wenn Sie alle Daten aus der Tabelle Archive.dbo.Records auswählen, ist die Zeile nicht mehr vorhanden, und Sie können die zugeordnete Datei nicht länger verwenden.

Hinweis

Die zugrunde liegenden Dateien werden vom FILESTREAM Garbage Collector entfernt.

Überprüfen, ob eine Tabelle oder Datenbank FILESTREAM-Daten enthält

Um herauszufinden, ob eine Datenbank oder Tabelle FILESTREAM-Daten enthält, müssen Sie die Systemansichten abfragen.

Das folgende erweiterte Beispiel zeigt die Schritte zum Erstellen einer neuen Datenbank, Erstellen von Tabellen mit FILESTREAM-Daten und Abfragesystemansichten, um festzustellen, ob die Tabellen und die Datenbank selbst FILESTREAM-Daten enthalten.

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

Freigeben über