February 7, 2012 at 3:31 pm
Hi,
I am planning to move an existing large table on SQL Server 2005 to a new file group.
Can you guys tell me if it is possible..if yes, could you please provide me the steps.
Thanks!
Siva.
February 7, 2012 at 3:35 pm
Yes. Use create index ... with drop_existing for the clustered index and specify the desired filegroup for the place that the index must be created on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 4:00 pm
Quick question Gail.
What happens to the non clustered index on those tables if you move the primary key index to a new filegroup using the option that you mentioned?
If we drop the primary key index using DROP and then later use CREATE index command, then I assume the non clustered indexes will be rebuilt twice.
Will CREATE INDEX with the DROP_EXISTING clause avoid this?
Blog
http://saveadba.blogspot.com/
February 7, 2012 at 4:04 pm
When doing this, make sure you know if any columns have off row data (LOB). If so, you will need to recreate the table in the new filegroup using the TEXTIMAGE_ON option on the create table statement. If you don't do this, then the filegroup you are moving out of will not reduce in size.
Here is a link for that syntax
http://msdn.microsoft.com/en-us/library/ms174979(v=sql.90).aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 4:21 pm
Thanks Guys...I am also curious to know what will happen to the non clustered indexes and waiting for Gail's answer on that one...
There are no LOBs on that table.
February 7, 2012 at 4:24 pm
You would need to move each NC index individually using the same method that Gail showed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 4:29 pm
savethytrees (2/7/2012)
What happens to the non clustered index on those tables if you move the primary key index to a new filegroup using the option that you mentioned?
I assume you mean the clustered index. Your PK can be nonclustered, and clustered index controls the Key Lookup information. The nonclustered indexes will remain on the old filegroup, where they were defined to be. They'd each need to be moved in the same way.
If we drop the primary key index using DROP and then later use CREATE index command, then I assume the non clustered indexes will be rebuilt twice.
Correct. Once to replace with RIDs, the other to rebuild it with the clustered index again.
Will CREATE INDEX with the DROP_EXISTING clause avoid this?
I... have no idea. I just bounced around a few locations looking for a definitive answer and couldn't find one. I do not believe it's necessary though, because it's occassionally used this way to rebuild an index, and that would be incredibly expensive to perform twice.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 7, 2012 at 4:34 pm
If the table is not too large then it should be fine to move the primary key index while the non clustered indexes are still available.
If the table is very big then I would suggest that you first drop the non clustered indexes. Then move the primary key index. Only after that you recreate the non clustered indexes on any filegroup that you want.
This way you rebuild the non clustered indexes only once.
I had moved the primary key to a different file group and I have documented what I did in the following link.
http://saveadba.blogspot.com/2012/02/primary-key-index-move-filegroup.html
I did not use the CREATE INDEX with DROP_EXISTING option. Hence, my question to Gail. The table that I was working with was really large and then vendor had created close to 30 non clustered indexes.
Blog
http://saveadba.blogspot.com/
February 7, 2012 at 4:42 pm
Evil Kraig F (2/7/2012)
I assume you mean the clustered index. Your PK can be nonclustered, and clustered index controls the Key Lookup information. The nonclustered indexes will remain on the old filegroup, where they were defined to be. They'd each need to be moved in the same way.
I meant all remaining non clustered index (not the clustered one regardless of whether it is primary key index or not).
Correct. Once to replace with RIDs, the other to rebuild it with the clustered index again.
I... have no idea. I just bounced around a few locations looking for a definitive answer and couldn't find one. I do not believe it's necessary though, because it's occassionally used this way to rebuild an index, and that would be incredibly expensive to perform twice.
That is why I wanted to know if DROP_EXISTING option will avoid rebuilding the remaining non clustered indexes twice.
Blog
http://saveadba.blogspot.com/
February 8, 2012 at 4:57 am
savethytrees (2/7/2012)
That is why I wanted to know if DROP_EXISTING option will avoid rebuilding the remaining non clustered indexes twice.
I think the following article explains it:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
in the "DROP_EXISTING Clause" section.
February 9, 2012 at 3:12 am
moving clustered index to a new filegroup after droping and recreating, implicitely change the non-clustered index references.
it is not required to re-create non clustered indexes again.
Cheers
Vikas
February 9, 2012 at 3:25 am
vikass1 (2/9/2012)
moving clustered index to a new filegroup after droping and recreating, implicitely change the non-clustered index references.
There's nothing that needs changing in the nonclustered index if the cluster is recreated (via create ... with drop_existing) with the same key columns and same uniqueness settings. Hence the nonclustered indexes won't be recreated or changed at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply