Alter Table Move TEXT_IMAGEON [NewFilegroup]
Currently to move BLOB data into a seperate filegroup requires dropping/creation of the current table.
Refer to BOL (Create TEXT_IMAGEON):
The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered.
Often as databases scale, it may be necessary to seperate BLOB data into different filegroups - currently this is time-consuming as may involve dropping many FK links to the table which contains the BLOB data.
Upvotes: 8<=-=Oct 10 2007 2:31AM=-=>
This may become even more important with filestream filegroup functionality in SQL 2008, allowing to move from an internal database filegroup to the filestream filegroup will be a valuable addition.<=-=Feb 1 2008 10:00AM=-=>
Thanks for your feedback. We will look into this during the planning for the next release. Your point about migrating text/image data to filestream is very compelling. Here is what you can do with SQL2008 and filestream
Let us say you have a table table t1 (c1 int, c2 varchar(max)). Now you can do the following
(1) add a varbinary(max) FILESTREAM column c3 and a rowguidcol column c4 to the table.
(2) update the table setting c3 = c2. This copies the data from the database files to the external files.
Then you can drop c2 and if you want rename c3 to c2.
I support the MS feedback for small tables only.
For larger tables, would currently have to: create a new table (with filestream defined), export the existing data out of the existing table, import the data into the new table, delete the old table (existing)
The copy/alter would otherwise leave blob data in the table page for that object which unfortunatly could be of significant size.
the BLOB data-types in question are: image, text, varbinary.
Currently I dont think these all of the older blob data-types can be supported via select <> from <><=-=Jul 3 2008 7:55AM=-=>
Did some testing,
with a test table.. table t1(c1 int, c2 text). Can convert “text” to “varchar(max)” then hopefully will be able to filestream the data.
If I rebuild the clustered index after the alter table statement, then this reduces the internal space required for holding the new/old table structures -refreshing the internal pages and only storing the new structures.
Large tables will take significant time migrating from one column to another running an update to external files etc.. esp with large tables ability to do online/offline would be appreciated.<=-=Oct 2 2009 10:24AM=-=>
DoButton: I did not realize that I did not post to it. Here is some question that I asked the Dev and the corresponding response.
Question: if the LOB data large, I am just wondering if this will generate too much logs? My take is that when we copy value to filestream, the actual data moved will not be logged as it is in FS. Also, when we drop C2, it is metadata only operation so data will not be logged�now the question is how do I get rid of excess data in C2. One way will be to rebuild the table?
Response from Dev>> You are right, copying data from c2 to c3 will not log it since c3 is in Filestream.
Yes, rebuilding the table is a good way to get rid of the excess data since Filestream data does not get copied on rebuilding an index. For heaps you can build a clustered index and drop it.<=-=Oct 30 2009 9:10AM=-=>
Should work with a filestream move for the data.
The only issue I thought was you couldnt have multiple filestreams for a table.
t1 (c1 int, c2 varchar(max) [on filestream: a], c3 varchar(max) [on filestream: a], c4 varchar(max) [on filestream: a])
t1 (c1 int, c2 varchar(max) [on filestream: a], c3 varchar(max) [on filestream: b], c4 varchar(max) [on filestream c])
Which is where the crux of the issue is:
Splitting a large tables/blobs into multiple destinations.
currently the only way of achieving this I can derive is splitting the table up into three seperate tables, and having a covering view so each table can access a different underlying filestream (quite complex).<=-=Dec 1 2014 5:14PM=-=>
Since this is quite old request and we have not addressed it, I want to close it. If you consider this to be a critical issue, please create a new connect item. The original request was to move LOB to its own FG without drop/create. with FS, you can do that. However, as you pointed out, all columns of the same table will stay on the same FG
Two ways can be used to bypass the problem :
1) create all your tables with a dummy BLOB column and specify TEXTIMAGE_ON with a dedicated filegroup, then, alter the table and drop the dummy column. This prepare the table to have all the LOBs stored into the dedicated FG.
2) alter the table so that it is partitioned in an unique partition stored on the dedicated BLOB filegroup, and then remove the partition by creating the clustered index on the original FG