In this bulletin, I will discuss two frequently asked questions that are centered around moving data between various database files. The instructions for accomplishing this task are available in SQL Server 2000 Books Online (BOL), but for various reasons these instructions may not be known to SQL Server support professionals and SQL Server customers alike.

There are many sources of information regarding how to optimize your disk, files, and filegroups configuration. The ones that are immediately available are usually the following:

• SQL Server 2000 Books Online
• SQL Server 2000 Operations Guide
• SQL Server 2000 Resource Kit

The question is typically phrased as "How do I move data between database files in order to get rid of some of them and reorganize the whole file structure?" The answer is to use DBCC SHRINKFILE with the EMPTYFILE option as described in BOL:

EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Microsoft SQL Server no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.

After executing the DBCC SHRINKFILE with the EMPTYFILE option, use the ALTER DATABASE statement with REMOVE FILE to get rid of the file that you no longer need.

Another interesting question is "How would a multiple file environment (one or more filegroups) influence performance?" A lot of information that addresses this question can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/thestorageengine.asp

A question that is commonly asked by SQL Server customers is this: "Is there an easy way to move existing tables and indexes to a new filegroup?" The answer to does exist in BOL, but it is not very easy to find. Here are the answers:

To place an existing index on a different filegroup (Enterprise Manager)

• Expand a server group, and then expand a server.
• Expand Databases, expand the database in which the table containing the index belongs, and then click Tables.
• In the Details pane, right-click the table, and then click Design Table.
• Right-click any column, and then click Properties.
• On the Indexes/Keys tab in the Selected index list, select the index to move.
• In the Index Filegroup list, select a filegroup on which to place the index.

To place an existing table on a different filegroup (Enterprise Manager)

• Expand a server group, and then expand a server.
• Expand Databases, expand the database in which the table belongs, and then click Tables.
• In the Details pane, right-click the table, and then click Design Table.
• Right-click any column, and then click Properties.
• On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.
• Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and text columns.

Here is a snapshot of where this is done in Enterprise Manager (SEM):

Here is how this feature works under the hood:

1. The moving of the index is actually dropping and recreating the index, specifying the new filegroup.
2. The moving of the table is quite a bit more complicated. It uses a second table (avoid calling it "temporary," because it's on the new filegroup and not on tempdb) to pump the data over, then drops the original and renames the new one to the original name. The trick is taking care of all the related constraints—SEM takes those into account, dropping and recreating them appropriately.

If you want to get more specific, use the SEM Query Spew or a Profiler trace, and you will be able to capture the actions taken. One interesting detail here (not very related)—SQL-DMO exposes a nice API call to script out objects (see the article "Script Method (Table Object)" in BOL for details).

Moving the table can be accomplished by creating a clustered index on the new filegroup. (Keep in mind that a table can only have one clustered index.) Here's what BOL has to say about this:

Note: Because the leaf level of a clustered index and its data pages are the same by definition, creating a clustered index and using the ON filegroup clause effectively moves a table from the file on which the table was created to the new filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. It is important that the filegroup has at least 1.2 times the space required for the entire table.

Obviously, the clustered index solution is more effective, and it works on a storage level, while SEM does it on a logical level. The only thing I could think of as an advantage to SEM's approach is the case where you have a large table to move—dropping and recreating the clustered index may turn out to be more expensive. In addition, if the table doesn't have a clustered index at all, this is also more expensive (obviously you need to drop the clustered index in the end).

As you can see, there are some SQL Server features that are not very well advertised, but they are very handy and they do exist. BOL is something like a treasure chest, and you can find some really cool stuff there. One of my favorites is the sample script for expanding a hierarchy from a self-joined table or expanding a network structure (see the article "Transact-SQL Tips" in BOL for details).

© 2003 Microsoft