Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2012-04-04 : 17:13:20
|
| Hi,If we have a clustered index or non-clustered index we can use create..index command with (drop_existing=on) to move to different filegroup without need to worry about constraints etc. But if we have a heap table with no clustered indexes and 2 non-clustered indexes, can we move heap to new filegroup without dropping and recreating table. One option is to create clustered index on new filegroup and then drop it and that will leave heap in new filegroup but that will involve recreating non-clustered indexes twice so we are wondering what are the other options.Thanks--rubs |
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-05 : 01:22:10
|
| I dont think there is an inbuilt support for that.You will have to script out the data to a new table placed on a new filegroup and then rename the new table with the old table.After Monday and Tuesday even the calendar says W T F .... |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-04-05 : 11:13:09
|
| One approach would be to drop the non-clustered indexes, create the clustered index in the new filegroup, drop the cluster and re-create the non-clustered indexes. Since the change between cluster and heap (in either direction) involves implicitly regenerating the non-clustered indexes, dropping the non-clustered indexes first will probably save you time.=================================================There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE) |
|
|
|
|
|
|
How to move table to different filegroup