You need to move multiple objects in off-hours??”all
the more reason to create objects on appropriate filegroups from the start.
To move objects between filegroups, use an ALTER statement, such as ALTER TABLE or
ALTER INDEX. The syntax requires you to drop the primary key constraint first; you??™ll need
to add it back immediately after moving the table to the new filegroup. Here I??™m simply
moving a table called Notes from its current location (the PRIMARY filegroup??”bad!) to the
DATA filegroup:
ALTER TABLE dbo.Notes
drop constraint PK_Notes
WITH (MOVE TO DATA)
Oddly enough, the ALTER TABLE statement won??™t work if you have no primary key
constraint to drop. At least that??™s how it behaves at the time of this writing. Still, this does
allow for a way to script moving multiple objects between filegroups in one pass.
Filegroup Strategies
BOL states, ???Most databases will work well with a single data file and a single transaction
log file.??? Normally, I find BOL to offer the best documentation for any software product,
period. In this case, I??™d rather that sentence be stricken from the record.
Pages:
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185