Just tried to move a 400 GB table into a new file group by rebuilding the clustered index. After the operation, only 200 GB of data was moved to the new file group.
That reminded me that, when rebuilding a clustered index into a new file group only the 'row' data will be moved. Any Large Object Value such as VARCHAR(MAX), XML, TEXT, VARBINARY(MAX) etc, will continue to be in the 'text file group' of the table, which can be configured only at the time of creating the table. To move all the data, including 'text data', the table needs to be re-created on the new file group and data has to be inserted explicitly.
Published under: SQL Server Tips · · · ·
On an related sidenote, you might check out this post by @SQLQueen
http://sqlblog.com/blogs/kalen_delaney/archive/2011/06/26/where-are-lobs-stored.aspx
That is very useful to know. Thank-you for sharing, Jacob!
Excellent reminder for alumni and great new info for neophytes, Jacob.
@Nitin... good link to amplify Jacob's thoughtful tip. Thanks for posting it.
Thanks Jeff, Nakul and Nitin!