Obtener acceso a datos FILESTREAM con Transact-SQL

Se aplica a:SQL Server

En este artículo se describe cómo usar las instrucciones Transact-SQL INSERT, UPDATEy DELETE para administrar datos FILESTREAM.

Insertar una fila que contiene datos FILESTREAM

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

Insertar un valor 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 resulta útil cuando se desea obtener un identificador de archivo, pero se va a manipular el archivo mediante 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. El 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, se añaden los datos 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.

Actualizar datos FILESTREAM

Puede usar Transact-SQL para actualizar los datos del archivo del sistema de archivos; pero posiblemente no quiera si hay que 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 Transact-SQL DELETE .

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 tabla Archive.dbo.Records, la fila desaparece y no se puede usar el archivo asociado.

Comprobar si una tabla o base de datos contiene datos FILESTREAM

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

En el ejemplo siguiente ampliado se muestran los pasos para crear una nueva base de datos, crear tablas que tengan 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

• Habilitar y configurar FILESTREAM
• Evitar conflictos con operaciones de base de datos en aplicaciones FILESTREAM