使用 Transact-SQL 存取 FILESTREAM 資料

文章
05/04/2023

適用於：SQL Server

本文說明如何使用 Transact-SQL、INSERT、UPDATE 與 DELETE 陳述式來管理 FILESTREAM 資料。

注意

本文中的範例需要使用在建立啟用 FILESTREAM 的資料庫和建立儲存 FILESTREAM 資料的資料表 (均為機器翻譯) 中，所建立之已啟用 FILESTREAM 的資料庫和資料表。

插入包含 FILESTREAM 資料的資料列

若要將資料列加入至支援 FILESTREAM 資料的資料表，請使用 Transact-SQL INSERT 陳述式。當您將資料插入 FILESTREAM 資料行時，可以插入 NULL 或 varbinary(max) 值。

插入 Null

下列範例將示範如何插入 NULL。 FILESTREAM 值為 NULL 時，資料庫引擎就不會在檔案系統中建立檔案。

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

插入長度為零的記錄

下列範例將示範如何使用 INSERT 來建立長度為零的記錄。當您想要取得檔案控制代碼，但是將要使用 Win32 API 操作檔案時，這會很有用。

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

建立資料檔案

下列範例將示範如何使用 INSERT 來建立包含資料的檔案。資料庫引擎會將字串 Seismic Data 轉換為 varbinary(max) 值。如果 Windows 檔案不存在，FILESTREAM 會建立此檔案。然後，資料會新增至資料檔案。

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

當您從 Archive.dbo.Records 資料表中選取所有資料時，其結果與下表所顯示的結果很相似。不過， Id 資料行將包含不同的 GUID。

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

更新 FILESTREAM 資料

您可以使用 Transact-SQL 來更新檔案系統檔案中的資料。不過，當您必須將大量資料串流處理至檔案時，可能不會想這樣做。

下列範例會以文字 Xray 1來取代檔案記錄中的所有文字。

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

刪除 FILESTREAM 資料

當您刪除包含 FILESTREAM 欄位的資料列時，您也會刪除其基礎檔案系統的檔案。因此，刪除資料列與檔案的唯一方法，是使用 Transact-SQL DELETE 陳述式。

下列範例將示範如何刪除資料列及相關聯的檔案系統檔案。

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

在您從 Archive.dbo.Records 資料表中選取所有資料時，此資料列就會消失，而且您無法再使用相關聯的檔案。

注意

基礎檔案會由 FILESTREAM 記憶體回收行程所移除。

檢查資料表或資料庫是否包含 FILESTREAM 資料

若要了解資料庫或資料表是否包含 FILESTREAM 資料，您必須查詢系統檢視。

下列擴充範例顯示的步驟，可用於建立新資料庫、建立具有 FILESTREAM 資料的資料表，以及查詢系統檢視，以查看資料表和資料庫本身是否包含 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';

分享方式：