5 File and Filegroup Tips

SQL Server 2000 and 7.0 let you create a database that's stored on multiple physical files and organized in one or more filegroups. Because files and filegroups aren't part of the ANSI SQL-92 definition, no information schema views contain information about these organizational structures. In my last T-SQL Admin column, "Accessing File and Filegroup Metadata," May 2002, InstantDoc ID 24677, I discussed SQL Server files and filegroups and presented some code for accessing system-table information about files and filegroups that you can't get by using Microsoft-supplied methods. You might also need to manipulate files and filegroups in ways that Microsoft-supplied methods such as information schema views or Enterprise Manager don't support. Some of these tasks don't necessarily require direct system-table access, but they don't have obvious solutions either. Here are answers to five common questions you might ask about these kinds of tasks. (For information about why you might want to create a database on multiple files or filegroups, see my SQL Server Magazine article "The Road to Recovery," September 2001, InstantDoc ID 21627, and Michael D. Reilly's Web-exclusive Certifiably SQL articles "Backup Strategies," August 21, 2000, InstantDoc ID 9629, and "Restoring Databases," September 24, 2000, InstantDoc ID 9808, at http://www.sqlmag.com.)

1. I just installed a new hard drive and want to place my most heavily accessed tables on it. How can I move a table or index from one filegroup to a filegroup on the new drive?

No command specifically moves a table to a new filegroup. However, recall that a clustered index's leaf level is the table data; so if you create (or recreate) a table's clustered index in a new filegroup, the table will automatically be relocated in the specified filegroup. The most straightforward way to recreate an existing clustered index is to use the CREATE INDEX command's DROP_EXISTING option, as the following syntax shows:

CREATE [UNIQUE] CLUSTERED INDEX <index name>
 ON <table name> (columns) 
    WITH DROP_EXISTING ON 
<filegroup name>...

If you use the two commands DROP INDEX and CREATE INDEX (without the DROP_EXISTING option) to recreate a clustered index, SQL Server completely rebuilds all the nonclustered indexes each time you use one of the commands. The row locators SQL Server uses in a nonclustered index are different depending on whether the table has a clustered index. So if you drop a clustered index, SQL Server rebuilds the nonclustered indexes to have the form they need for an underlying heap table (a table without a clustered index). Then when you rebuild the clustered index, SQL Server must rebuild all the nonclustered indexes again.

But if you use the DROP_EXISTING option, as the above example shows, SQL Server realizes that the lack of a clustered index is temporary. In SQL Server 7.0, if you use the DROP_EXISTING option to rebuild a clustered index on the same columns, the nonclustered indexes won't change. However, in SQL Server 2000 release to manufacturing (RTM) and Service Pack 1 (SP1), SQL Server rebuilds the nonclustered indexes once. This behavior, which SP2 fixes, is a bug documented in the Microsoft article "FIX: Nonclustered Indexes Rebuilt For CREATE UNIQUE CLUSTERED INDEX ... WITH DROP_EXISTING Query" (http://support.microsoft.com/default.aspx?scid=kb;en-us;q304519). However, even without the SP2 fix, using the DROP_EXISTING solution is still a more efficient method than the double rebuild needed when you don't use the DROP_EXISTING option. Just remember that the data follows the clustered index: The filegroup that contains the clustered index also contains the data.

2. I have a table that contains several text and image columns that are rarely accessed. I don't want to use space on my fastest drives to store this little-used large object data. Can I place the data on a filegroup that's separate from the rest of the table's data?

In addition to using the DROP_EXISTING option to place tables and indexes in a specific filegroup, you can also use the keyword TEXTIMAGE_ON with the CREATE TABLE command to place the table's large object data (text, ntext, or image data type) into a specific filegroup. SQL Server supports this capability only when you first create a table. SQL Server doesn't provide a way to change the location of the large object data without completely rebuilding the table. For example, you can't just use the ALTER TABLE command to drop the text or image column, then use the ALTER TABLE command again to add it back because ALTER TABLE doesn't support the TEXTIMAGE_ON keyword. Also, because large object data is generally stored on extents that are separate from the rest of the table data, moving the clustered index won't affect the storage location of text or image data.

You can use the sp_help system stored procedure to learn which filegroup the table data is in and which filegroup each index is in, but sp_help won't tell you which filegroup the text or image data is in. For that information, you have to directly access the system tables. Information about the storage of large object data is in a row in sysindexes that has an indid value of 255. The name that sysindexes shows is the name of the table preceded by the letter t.