Moving SQL table text/image to a new filegroup

Another post for ASP.NET/SQL developers reading this blog. If you think these posts do not belong here, please leave a comment, and I'll consider moving my development articles to a separate blog.

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.

by Alex. CEO, founder

comments