Acessar dados FILESTREAM com Transact-SQL

  • Artigo

Aplica-se a:SQL Server

Este artigo descreve como usar as instruções INSERT, UPDATE e DELETE do Transact-SQL para gerenciar dados FILESTREAM.

Observação

Os exemplos neste artigo exigem o banco de dados e a tabela habilitados para FILESTREAM criados em Criar um banco de dados FILESTREAM-Enabled e criar uma tabela para armazenar dados FILESTREAM.

Inserir uma linha que contém dados FILESTREAM

Para adicionar uma linha a uma tabela que dá suporte a dados FILESTREAM, use a instrução INSERT Transact-SQL. Ao inserir dados em uma coluna FILESTREAM, é possível inserir NULL ou um valor varbinary(max) .

Inserir NULL

O exemplo a seguir mostra como inserir NULL. Quando o valor FILESTREAM é NULL, o Mecanismo de Banco de Dados não cria um arquivo no sistema de arquivos.

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

Inserir um registro de comprimento zero

O exemplo a seguir mostra como usar INSERT para criar um registro com comprimento zero, o que é útil quando você quer obter um identificador de arquivo, mas manipulará o arquivo usando APIs de Win32.

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

Criar um arquivo de dados

O exemplo a seguir mostra como usar o objeto INSERT para criar um arquivo que contenha dados. O Mecanismo de Banco de Dados converte a cadeia de caracteres Seismic Data para um valor varbinary(max) . FILESTREAM criará o arquivo do Windows se ele ainda não existir. Em seguida, os dados são adicionados ao arquivo de dados.

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

Quando você selecionar todos os dados da tabela Archive.dbo.Records, os resultados serão similares aos mostrados na tabela a seguir. Porém, a coluna Id conterá GUIDs diferente.

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

Atualizar dados FILESTREAM

Você pode usar o Transact-SQL para atualizar os dados no arquivo do sistema de arquivos, mas talvez não queira ao transmitir grandes quantidades de dados para um arquivo.

O exemplo a seguir substitui qualquer texto no registro do arquivo pelo texto Xray 1.

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

Excluir dados FILESTREAM

Ao excluir uma linha que contém um campo de FILESTREAM, você também exclui seus arquivos subjacentes do sistema de arquivos. A única maneira de excluir uma linha e, portanto, o arquivo, é usar a instrução TRANSact-SQL DELETE.

O exemplo a seguir mostra como excluir uma linha e seus arquivos associados do sistema de arquivos.

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

Quando você seleciona todos os dados da Archive.dbo.Records tabela, a linha desaparece e você não pode mais usar o arquivo associado.

Observação

Os arquivos subjacentes são removidos pelo coletor de lixo do FILESTREAM.

Verificar se uma tabela ou banco de dados contém dados FILESTREAM

Para descobrir se um banco de dados ou tabela contém dados FILESTREAM, você deve consultar as exibições do sistema.

O exemplo estendido a seguir mostra as etapas para criar um novo banco de dados, criar tabelas que tenham dados FILESTREAM e consultar exibições do sistema para ver se as tabelas e o próprio banco de dados contêm dados 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';

Confira também