tsql - 如何在 T-SQL 中将带有文件流的 varbinary(max) 转换为实际的 varbinary(max)

Question

我有一个数据库，该数据库被设置为对 varbinary(max) 字段上的音频文件使用 blob FileStream。它的大小已经增长到 80GB 以上，我面临着性能问题。

环顾四周后，我发现我的平均 blob 大小约为 180k。并且因为根据MSDN文件流应该用于超过 1MB 的对象，我正在重新评估我如何存储这些 blob。 MSDN 还指出，“对于较小的对象，在数据库中存储 varbinary(max) BLOB 通常可以提供更好的流性能。”所以我正在考虑从带有文件流的 varbinary(max) 转移到只使用 varbinary(max) 字段。

所以我的问题是，是否有使用 sql 脚本将每个文件流 blob 从文件流移动到实际 varbinary 字段本身的好方法？在决定询问之前我一直在研究的替代方法是让 c# 应用程序查询数据库中的 blob 并将每个 blob 写入文件系统。然后从数据库中手动删除文件流内容。然后让 c# 应用程序从文件系统读取 blob 并写回数据库。我认为必须有更简单的方法。

Answer 1

假设您的源表如下所示:

CREATE TABLE audioFiles
(
    AudioID INT IDENTITY NOT NULL PRIMARY KEY,
    [Name] VARCHAR(50) NOT NULL,
    [AudioData] VARBINARY(MAX) FILESTREAM NULL,
    RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT(NEWID())
)

然后你可以创建第二个表:

CREATE TABLE audioBlobs
(
    AudioID INT IDENTITY NOT NULL PRIMARY KEY,
    [Name] VARCHAR(50) NOT NULL,
    [AudioData] VARBINARY(MAX) NULL,
    RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT(NEWID())
)
GO

(请注意，第二个表的 AudioData 列中缺少 FILESTREAM ......导致二进制数据与记录的其余部分一起存储在页面上，而不是单独的 FILESTREAM 文件组中。)

然后你可以将数据从一个表插入到另一个:

SET IDENTITY_INSERT audioBlobs ON

INSERT INTO audioBlobs (AudioID, Name, AudioData, RowGuid)
    SELECT AudioID, Name, AudioData, RowGuid FROM audioFiles

SET IDENTITY_INSERT audioBlobs OFF

完成后，您可以删除原始表，并将新表重命名为原始表的名称:

DROP TABLE audioFiles
GO

EXECUTE sp_rename N'dbo.audioBlobs', N'audioFiles', 'OBJECT' 
GO

或者，您可以创建第二个 VARBINARY(MAX)您的 FILESTREAM 旁边的列列，然后使用旧列的数据更新新列的值。请注意，无论采用哪种方式，您的总磁盘空间使用量都会增加一倍以上——将实际音频数据的空间增加一倍，将其从 FILESTREAM 文件组迁移到 PRIMARY 文件组(或主数据文件所在的任何位置)，再加上一个您的事务日志中有很多空间。