Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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'
share|improve this answer
    
Excellent - thanks Massimo - just what I needed to know! –  user1098487 Oct 2 '12 at 22:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.