Moving Tables Between Filegroups: A Better Way

Filegroups are useful tools for controlling a database’s location and size because they let DBAs easily move files between different disks or network computers. But using filegroups can present problems when you need to move tables because if you define multiple filegroups for a single database, then some tables can reside in one filegroup while other tables might be stored in a different filegroup. If you then need to move data files, the fact that the data is spread out among filegroups means you’ll likely have to move tables from one filegroup to another. Here’s a sample scenario: A database called \[MyDB\] has two filegroups, which the sample code in Listing 1 shows. The first filegroup, called \[PRIMARY\], contains the data file C:\MyDB_Primary.mdf; the second filegroup, \[MyFileGroup\], has the data file D:\MyDB_ MyFilegroup.ndf. If I want to move all the table data to drive D while maintaining 100 percent uptime (in which case detaching databases or taking files offline is not an acceptable option), then I must move tables from one filegroup to the other. The need to move tables arises in other situations, such as in filegroup consolidation or before deleting database files. To make moving tables between filegroups easier and more efficient, I’ve written a SQL script that moves one table between filegroups. The script doesn’t assume any prior knowledge about the table and can be used to move tables that have a clustered index and also to move heaps. In addition, the script lets users specify whether a table’s nonclustered indexes and unique constraints should be moved as well.

Related: Focus on Files and Filegroups

My script is useful for moving tables or indexed views for SQL Server 2005 and SQL Server 2000. If a table you want to move has full-text indexes, the script won’t register those indexes with the full text catalog, nor will the script move user-defined statistics. Note that although I composed the script to move single tables, you can easily modify it to move a collection of tables and indexed views. Before I explain how my script works, let’s take a look at the basics of tables and moving them between filegroups. (The description following applies as well to indexed views because indexes on views have the same underlying architecture that table indexes do.)

Understanding Table Types

A table is stored on the filegroup that holds its data pages. In order to move a table between filegroups and keep the table schema intact, it’s important to distinguish between two types of tables. The first table type refers to tables that have a clustered index. When a table has a clustered index, the data pages are the leaf pages of the clustered index B-tree. To move such tables, you must recreate the clustered index on the target filegroup and also move the nonclustered indexes and constraints. The second table type refers to heaps. With tables that don’t have a clustered index, you can move the data pages by dropping the table, then recreating it on the desired file group. A better solution that omits the need to drop the table creates a clustered index on the target filegroup, then drops the clustered index later.

Although it might seem from the foregoing explanation that tables can be moved easily, keep in mind that changing clustered indexes can negatively affect a server’s performance and thus the availability of data, especially in large tables. When it comes to moving tables, the devil truly is in the details.

Existing Table-Moving Techniques

Assume that the database \[MyDB\] contains the table \[dbo\].\[MyTable1\]. This table has a primary key that is also a clustered index, a nonclustered index, and a unique constraint (the Data Definition Language—DDL—for the table is provided in the sample code in Listing 2). The indexes and constraints for this table all reside on the \[PRIMARY\] filegroup—we can validate this by running the SQL code in Listing 3 against the \[MyDB\] database. This SQL statement returns the filegroup information for all indexes and constraints that belong to the table \[dbo\].\[MyTable1\].

One technique for moving a table to a different filegroup is to use Enterprise Manager in SQL Server 2005 or SQL Server 2000. In Enterprise Manager you can right-click the table, select Design Table, click the Properties icon, then select the desired filegroup from the Table Filegroup dropdown menu, as Figure 1, page 38, shows.

To move the table’s indexes and constraints, you can go to the Indexes/Keys tab on the Properties page, as Figure 2, page 38, shows, change the filegroup for each object in the Index Filegroup dropdown menu, click Close, then click Yes when prompted to apply the changes. Unfortunately, the ability to move a table to a different filegroup from the UI is no longer available in SQL Server Management Studio.

This technique’s main benefit is that it’s straightforward. However, a quick look under the hood reveals that it isn’t particularly efficient: Moving several tables requires a lot of tedious and manual work. Moreover, if you were to run SQL Profiler during the table move, you’d see that the GUI creates a new table called \[dbo\].\[Tmp_utbMyTable1\], moves the data from \[dbo\].\[utbMyTable1\] to the new table, then makes the new table assume the identity of the original one. These actions are inefficient because the table data is duplicated and stored twice in the database (once in \[dbo\].\[utbMyTable1\] and once in \[dbo\].\[Tmp_utbMy- Table1\]), which is extremely wasteful, especially in the case of large tables. Another problem with this method is that if the table \[dbo\].\[utbMyTable1\] has user-defined statistics, those statistics are dropped without warning and are not recreated. These actions are embedded in Enterprise Manager and in the SQL Distributed Management Objects (DMO)—both for SQL Server 2000 and the SQL Server 2005–compatible DMO—and therefore can’t be controlled by the user.

Another simple and popular way to move tables with a clustered index is to recreate the clustered index using the CREATE INDEX command together with the DROP_EXISTING clause, as the sample code in Listing 4, page 38, illustrates. This method is quick and easy; however, it results in redundant and wasteful operations. The SQL statement in Listing 4 moves only the clustered index and therefore has to be followed by similar statements for all nonclustered indexes. The recreation of the clustered index forces a rebuild of all nonclustered indexes and B-trees because the leaf pages of the nonclustered indexes must be redirected to point to the leaf pages of the newly reconstructed clustered index. In other words, using this technique rebuilds the nonclustered indexes twice—once when the clustered index is moved and again when the nonclustered indexes are recreated on the target filegroup.

A More Efficient Solution

As I’ve mentioned, the script I’ve written can move any type of table, including heaps. Here’s how it operates. The script accepts five user-assigned parameters: The variables @SourceFileGroupID and @TargetFileGroupID are set by the user and hold the original filegroup ID for the table to be moved, as well as the ID of the target filegroup. You can obtain the IDs of the database file groups by running EXEC sp_helpfile or by querying the system object \[sysfiles\]. Before running the script, you should also specify the name of the table to move as the variable @TableToMove. Moreover, you can indicate whether the primary key and constraints should also be moved by setting the value of the bit variable @MovePKAndAllUniqueConstraints to 1. In the same way, the bit variable @MoveAllNon- ClusteredIndexes indicates whether to move the nonclustered indexes. In order to move the table, the script performs the following steps and actions. First, the script determines whether the source and target filegroups and the requested table indeed exist, and aborts if they do not. Then, the script checks whether the requested table has a clustered index or is a heap. If the table is a heap, the script determines whether the table contains a unique column (i.e., an identity column or a column with a primary key or unique constraint). If a unique column indeed exists, the script creates a nonunique clustered index on that column on the appropriate target filegroup. Applying the clustered index on a unique column is useful because the column data has high cardinality even if duplicate data entries exist (e.g., as in the case of manually populating an identity column with SET IDENTITY INSERT), which guarantees fast and efficient creation of the clustered index. In the event that no unique columns are available, the script adds a new identity column and builds a clustered index on it. All items that are added to the table as part of the move process are later dropped to ensure that the table schema remains unchanged.

In a case where the requested table has a unique clustered index, the script will first drop all foreign keys that reference the unique index. (If the clustered index is not unique, then there’s no need to drop foreign keys.) Next, the clustered index is dropped. If you instruct the script to move the nonclustered indexes and the table constraints, they will be dropped to avoid the duplicate index rebuild I described previously. Finally, the script creates the clustered index on the target filegroup and also recreates all the indexes, constraints, and keys that were dropped earlier while maintaining all properties and attributes for all objects (such as PADINDEX and FILLFACTOR).

Moving IMAGE and TEXT Columns to Other Filegroups in SQL Server 2000

In SQL Server 2000 we have the option to explicitly specify the filegroup for columns that use long data types (i.e., IMAGE, TEXT, NTEXT) by using the TEXTIMAGE_ ON clause. For example, we can change the table creation statement in Listing 1 to write:

CREATE TABLE \[dbo\].\[utbMyTable1\]
(\[Col1\] INT IDENTITY(1, 1),
\[Col2\] NVARCHAR(10),
\[Col3\] BIGINT,
\[Col4\] IMAGE) ON \[PRIMARY\] TEXTIMAGE_ON \[MyFilegroup\]

in which case the IMAGE data stored in column Col4 would reside on the filegroup \[MyFilegroup\]. If a table has more than one column that utilizes a large data type, then the data for all such large columns will be stored on the filegroup specified in the TEXTIMAGE_ON section. In SQL Server 2005 the TEXTIMAGE_ON clause is no longer available—the data contained in IMAGE, XML, TEXT, NTEXT, VARBINARY(MAX), VARCHAR(MAX), and NVARCHAR(MAX) columns always resides on the same filegroup as the table’s data pages. It’s important to note that my script doesn’t move data for the column types specified above because the only way to move these data entries is by dropping and recreating the entire table.

Limitations and Possibilities

My script doesn’t handle the case in which two objects in the database have the same name and belong to different schemas. On top, the script handles one table or indexed view at a time. I encourage interested readers to tune the script to move multiple objects and handle richer scenarios to fit their database environments.

Learn more: Using T-SQL To Automatically Move Tables