Can someone specify detail on how to do this
-
I read from BOL
If there is a clustered index on a table, the data and the clustered index always reside in the same filegroup. Therefore, you can move a table from one filegroup to another by creating a clustered index on the base table that specifies a different filegroup on which to create the index (the index can then be dropped, leaving the base table in the new filegroup).
I am not understand how to separate clustered index also if this is primary key and how can I separate on different filegroup ?
Thanks
feifei liu
Rachel -
The clustered index is the data. So if you move it, then the data moves. The PK may or may not be the clustered index. They are not related, though you can make them the same.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Your answer I knew, but BOL say we can separate clustered index and table to different filegroup, how's base table how separate, I like someone explain detail.
Thanks
feifei liu
Rachel -
Can you post where you have found this in BOL?
I always thought that the leaf level of a clustered index contains not only the index keys, but the data pages.
Just tried to add another filegroup to Northwind db, moved the index PK_Categories to the second filegroup, I then looked in EM at the properties of the table Categories and found it on the second filegroup.
Don't be confused by EM stating TextFileGroup on Primary. This is something different for text or ntext columns. The actual table data has moved.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The first my post I copied from BOL, and I test it I can create table on primary filegroup and I create clustered index on secondary filegroup, then I look at sysindex table with indid id 0 or 1 and found clustered index on secondary filegroup, but where I check table on which filegroup, I think check sysindex table also, so I checked and looks like all table move to secondary filegroup
Any idea?
Thank you for reply
feifei liu
Rachel -
quote:
Your answer I knew, but BOL say we can separate clustered index and table to different filegroup, how's base table how separate, I like someone explain detail.Thanks
feifei liu
BOL (that you quoted) says you can not separate the data and its clustered index on different filegroups, which is, of course, correct.
--Jonathan
--Jonathan -
I wrote a script that moves a table between filegroups automatically for you. You can find the script at
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
------------------------
Omri Bahat
SQL Farms Solutions
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
-
Just out of curiosity: Are you going to dig out all the old threads where this what the topic?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Meine liebe Frank,
I aim to help all those in need of better SQL scripts than those available out there.
If you have any issues with that, I would more than happy to hear about it.
Please feel free to email me at omri@sqlfarms.com
auf wiedersehen,
Omri.
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply