Existing tables with binaries to use filestream

Question

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.

score 7 · Accepted Answer · answered Oct 01 '12 at 21:19

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'

Excellent - thanks Massimo - just what I needed to know! – user1098487 Oct 02 '12 at 22:33 — user1098487, Oct 02 '12 at 22:33

Existing tables with binaries to use filestream

1 Answers1