如何在T-SQL中将带有filestream的varbinary(max)转换为实际的varbinary(max)？

Question

我有一个数据库，设置了一个blob FileStream用于存储音频文件的varbinary(max)字段。它现在已经增长到80GB以上，我面临性能问题。

经过一番搜索，我发现我的平均blob大小约为180k。根据MSDN的说法，filestream应该用于超过1MB的对象，因此我正在重新评估如何存储这些blob。MSDN还指出：“对于较小的对象，在数据库中存储varbinary(max) BLOB通常提供更好的流式传输性能。”因此，我考虑从带有filestream的varbinary(max)转移到只使用varbinary(max)字段。

所以我的问题是，是否有一个很好的方法，使用sql脚本将每个filestream blob从filestream移动到实际的varbinary字段中？另一种选择是，在决定询问之前，我一直在努力开发一个C#应用程序查询数据库中的blob，并将每个blob写入文件系统。然后手动从数据库中删除filestream内容。然后让C#应用程序从文件系统读取blob并写回到数据库中。我想肯定有更简单的方法。

- Levitikon

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

或者，您可以在原始表中的FILESTREAM列旁边创建第二个VARBINARY(MAX)列，并只更新新列的值以包含旧列的数据。请注意，无论哪种方式，您都将使总磁盘空间使用量增加超过两倍--双倍于实际音频数据的空间，将其从FILESTREAM文件组迁移到主文件组（或其他存储主数据文件的位置），再加上大量事务日志中的空间。

- Dusty

---

NEWID() 不适合用于索引。最好使用 NEWSEQUENTIALID()。 - coding Bott