sql-server - 将文件流添加到现有表列

Question

我有下表列:

[Content] [varbinary](max) NULL

我想将其设为文件流列，所以我尝试了:

alter table dbo.Files
  alter column Content add filestream

但我收到错误:

Incorrect syntax near 'filestream'.  

我也尝试过

alter table dbo.Files
  alter column Content varbinary(max) filestream not null

但我收到错误:

Cannot alter column 'Content' in table 'Files' to add or remove the FILESTREAM column attribute.

如何将文件流添加到现有列？

Answer 1

您需要执行以下操作(源自here):

/* rename the varbinary(max) column
eg. FileData to xxFileData */
sp_RENAME '<TableName>.<ColumnName>', 'xx<ColumnName>' , 'COLUMN'
GO

/* create a new varbinary(max) FILESTREAM column */
ALTER TABLE <TableName>
ADD <ColumnName> varbinary(max) FILESTREAM NULL
GO

/* move the contents of varbinary(max) column to varbinary(max) FILESTREAM column */
UPDATE <TableName>
SET <ColumnName> = xx<ColumnName>
GO

/* drop the xx<ColumnName> column */
ALTER TABLE <TableName>
DROP COLUMN xx<ColumnName>
GO