Sql-server – Existing tables with binaries to use filestream

filestreamsql serversql-server-2008

I've got a few tables for which I want to use filestream storage.

These tables already contain binary data and have rowguids. However at the time they were were created, the tables were not added to a filestream enabled filegroup.

What is the best way to have these tables use filestream at this point? Do I need to drop + recreate the tables and migrate the data? Is there an easier way?

The database already has filestream enabled and there are other tables which are using them.

Best Answer

You don't need to add the whole table to a filestream filegroup, and indeed you can't, because only filestream columns get stored in filestream storage; other columns in the same table go to normal storage.

First of all, you need to choose which filestream filegroup you want to use to store the table's filestream data:

ALTER TABLE YourTable SET (FILESTREAM_ON = FSGroup)

Then you can simply add filestream columns to the table:

ALTER TABLE YourTable ADD YourColumn varbinary(max) FILESTREAM

However, you can't ALTER an existing column to start using filestream if it wasn't defined that way; but you can create a new column, copy the data and then drop the old column and rename the new one:

ALTER TABLE YourTable ADD NewColumn varbinary(max) FILESTREAM
UPDATE YourTable SET NewColumn = OldColumn
ALTER TABLE YourTable DROP COLUMN OldColumn
EXEC sp_rename 'NewColumn','OldColumn','COLUMN'
Related Topic