Acceso a datos FILESTREAM con Transact-SQL

  • Artículo

Se aplica a:SQL Server

En este artículo se describe cómo usar las instrucciones INSERT, UPDATE y DELETE de Transact-SQL para administrar los datos filestream.

Nota:

Los ejemplos de este artículo requieren la base de datos y la tabla habilitadas para FILESTREAM que se crean en Crear una base de datos de FILESTREAM-Enabled y Crear una tabla para almacenar datos FILESTREAM.

Insertar una fila que contenga datos FILESTREAM

Para agregar una fila a una tabla que admita datos FILESTREAM, use la instrucción INSERT de Transact-SQL. Al insertar datos en una columna FILESTREAM, se puede insertar NULL o un valor varbinary(max) .

Insertar NULL

El ejemplo siguiente muestra la forma de utilizar el valor NULL. Cuando el valor filestream es NULL, el motor de base de datos no crea un archivo en el sistema de archivos.

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

Insertar un registro de longitud cero

En el siguiente ejemplo se muestra cómo utilizar INSERT para crear un registro de longitud cero. Esto es útil cuando se desea obtener un identificador de archivo, pero el archivo se va a tratar con las API de Win32.

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

Crear un archivo de datos

En el siguiente ejemplo se muestra cómo utilizar INSERT para crear un archivo que contiene datos. Motor de base de datos convierte la cadena Seismic Data en un valor varbinary(max) . FILESTREAM crea el archivo de Windows si aún no existe. A continuación, los datos se agregan al archivo de datos.

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

Al seleccionar todos los datos de la tabla Archive.dbo.Records, los resultados son similares a los que se muestran en la tabla siguiente. Sin embargo, la columna Id contendrá GUID diferentes.

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

Actualización de datos FILESTREAM

Puede usar Transact-SQL para actualizar los datos en el archivo del sistema de archivos, pero es posible que no quiera transmitir grandes cantidades de datos a un archivo.

En el ejemplo siguiente se reemplaza cualquier texto del registro del archivo por el texto Xray 1.

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

Eliminar datos filestream

Al eliminar una fila que contiene un campo FILESTREAM, también elimina sus archivos de sistema de archivos subyacentes. La única manera de eliminar una fila y, por tanto, el archivo, es usar la instrucción DELETE de Transact-SQL.

El ejemplo siguiente muestra cómo eliminar una fila y sus archivos de sistema de archivo asociados.

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

Al seleccionar todos los datos de la Archive.dbo.Records tabla, la fila desaparece y ya no puede usar el archivo asociado.

Nota:

El recolector de elementos no utilizados de FILESTREAM quita los archivos subyacentes.

Comprobar si una tabla o base de datos contiene datos FILESTREAM

Para averiguar si una base de datos o tabla contiene datos FILESTREAM, debe consultar las vistas del sistema.

En el ejemplo extendido siguiente se muestran los pasos para crear una nueva base de datos, crear tablas que tienen datos FILESTREAM y consultar vistas del sistema para ver si las tablas y la propia base de datos contienen datos 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';

Consulte también