Accedere ai dati FILESTREAM con Transact-SQL

Articolo
03/24/2024

Si applica a: SQL Server

Questo articolo descrive come fare a usare le istruzioni Transact-SQL INSERT, UPDATE e DELETE per gestire dati FILESTREAM.

Nota

Gli esempi riportati in questo articolo richiedono il database e la tabella abilitati per FILESTREAM creati in Creare un database abilitato per FILESTREAM e Creare una tabella per archiviare dati FILESTREAM.

Inserimento di una riga che contiene dati FILESTREAM

Per aggiungere una riga a una tabella che supporta i dati FILESTREAM, usa l'istruzione INSERT di Transact-SQL. Quando si inseriscono dati in una colonna FILESTREAM, è possibile inserire NULL o un valore varbinary(max) .

Inserisci NULL

Nell'esempio seguente viene illustrato come inserire il valore NULL. Se il valore FILESTREAM è NULL, il motore di database non crea alcun file nel file system.

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

Inserimento di un record di lunghezza zero

Nell'esempio seguente viene illustrato l'utilizzo di INSERT per creare un record di lunghezza zero. Questa istruzione risulta utile quando si desidera ottenere un handle di file, ma si modifica il file utilizzando API Win32.

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

Creare un file di dati

Nell'esempio seguente viene illustrato l'utilizzo di INSERT per creare un file contenente dati. Il motore di database converte la stringa Seismic Data in un valore varbinary(max). FILESTREAM crea il file di Windows, se non esiste già. I dati vengono quindi aggiunti al file di dati.

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

Se si selezionano tutti i dati della tabella Archive.dbo.Records, i risultati sono analoghi a quelli illustrati nella tabella seguente. La colonna Id conterrà tuttavia GUID diversi.

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

Aggiornamento di dati FILESTREAM

Per aggiornare i dati in file del file system, è possibile usare Transact-SQL, sebbene sia preferibile evitare questa procedura quando si devono trasmettere elevate quantità di dati a un file.

Nel seguente esempio il testo nel record del file viene sostituito con il testo Xray 1.

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

Eliminazione di dati FILESTREAM

Quando si elimina una riga che contiene un campo FILESTREAM, vengono eliminati anche i file del file system sottostanti. L'unico modo per eliminare una riga e pertanto il file è l'utilizzo dell'istruzione DELETE di Transact-SQL.

Nell'esempio seguente viene descritta l'eliminazione di una riga e dei file del file system associati.

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

Quando selezioni tutti i dati della tabella Archive.dbo.Records, la riga viene rimossa e non puoi più usare il file associato.

Nota

I file sottostanti vengono rimossi dal Garbage Collector di FILESTREAM.

Controllare se una tabella o un database contiene dati FILESTREAM

Per determinare se un database o una tabella contiene dati FILESTREAM, devi eseguire query sulle visualizzazioni di sistema.

Nell'esempio esteso seguente vengono illustrati i passaggi per creare un nuovo database, creare tabelle con dati FILESTREAM ed eseguire query sulle visualizzazioni di sistema per verificare se le tabelle e il database stesso contengono dati 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';

Condividi tramite