Moving SQL table text/image to a new filegroup

This post has been updated, scroll to the bottom.

When you move an SQL-table to a new filegroup via the clustered-index recreation procedure, only "elementary" datatypes are moved (like "int", "datetime", "varchar" etc). Text, image, varbinary(max) and similar columns (aka "BLOB-fields") will stay at their current location. If you click the "storage" settings for the table under the "properties" context menu item, you'll see that the "text filegroup" is the same as before! More info

The "text filegroup" can't be changed without re-creating a table. And I bet you've seen this phrase a million times while googling for a solution, right? Ok, we need to re-create the table and copy all the data into this new table. But who wants to do this manually? Drop and re-create a table with all the indexes, foreign-keys, primary keys, identities... Ain't there an automated procedure for this?

There is. But it took me hours to find it in "SQL Management Studio". Here are the steps:

0. In MS SQL Management Studio click "Tools - Options - Designer" and UNcheck the "Prevent saving changes that require table re-creation" box.

1. Right-click the table and select "Design" for SQL 2008 or "Modify" for SQL 2005.

2. Press F4 to open the "properties" window.

3. Make sure that you have the table (!) selected on top of the "properties" window.

4. Change the "text filegroup" for the table.

5. Wait! DO NOT save your changes. The SQL-server most likely will throw a timeout error for your large table.

6. Instead choose "Generate change script" and copy the resulting text to the new query window.

7. Run the query.

8. Be patient. I just ran this on our helpdesk database, for the table that holds 39 GB of data and it took more than 2 hours to complete.

Update from 2021

The above method still works, but on modern versions of SQL Server these days you can do this online (!) using just scripts. The trick is to use partitioning - a built-in mechanism that allows moving data between filegroups automatically.

Suppose you have a MyTable table that has an "ID" column and a LOB field "Data". Here are the steps:

1. Move the clustered index (moves everything but the LOB field):

   CREATE UNIQUE CLUSTERED INDEX PK_MyTable
   ON MyTable(ID)
   WITH (DROP_EXISTING = ON, ONLINE = ON)
   ON SECONDARY

2. Get the maxmum ID value SELECT MAX(ID) FROM MyTable and memorize the result. Let's say it's 9999
3. Create a partitioning function for a slightly bigger value:

   CREATE PARTITION FUNCTION pf_MyTable_temp (INT)
   AS RANGE RIGHT FOR VALUES (10100)

4. Create partitioning scheme based on this function

   CREATE PARTITION SCHEME ps_MyTable_temp
   AS PARTITION pf_MyTable_temp
   TO ( [SECONDARY], [PRIMARY] )

5. Recreate clustered index based on this partitioning

   CREATE UNIQUE CLUSTERED INDEX PK_MyTable
   ON MyTable(ID)
   WITH (ONLINE = ON, DROP_EXISTING = ON)
   ON ps_MyTable_temp (UserID)

6. At this point the data is moved to SECONDARY, but it's all based on our partitioning scheme
7. Recreate the clusetered index again - without the partitioning

   CREATE UNIQUE CLUSTERED INDEX PK_MyTable
   ON MyTable(ID)
   WITH (DROP_EXISTING = ON, ONLINE = ON)
   ON SECONDARY

8. That's it! We can drop the function and the scheme now.

   DROP PARTITION SCHEME ps_MyTable_temp;
   DROP PARTITION FUNCTION pf_MyTable_temp;

Alex Yumashev
Alex has founded Jitbit in 2005 and is a software engineer passionate about customer support.

Subscribe comments Tweet