:pinch: I'm sorry but as written this is wrong.
The reason why a clustered index ALWAYS goes to the same filegroup the table goes is because the leaf level of the index is actually sitting inside the base table.
I never claimed that the clustered index will be on a different file group then the table. I do claim however that that when I create a clustered index, it doesn’t have to be created the same file group that the table already exists on. Of course when I create it on a different file group, the table will be transferred to the file group that the new clustered index will be created on. This means that sometime the clustered index will be created on the same file group as the table, but other times it will be created on a different file group and the table will be moved to this file group. Here is a small demo, that shows it:
--Creating a database with few file groups
CREATE DATABASE TestDB
ON
PRIMARY
(NAME = PrimaryFile,
FILENAME = 'c:\TestDB.mdf',
SIZE = 100MB),
FILEGROUP DefaultFileGroup
( NAME = DefaultFile,
FILENAME = 'c:\DefaultFile.ndf',
SIZE = 100MB)
LOG ON
(NAME = DemoDBLog,
FILENAME = 'c:\TestDB.ldf',
SIZE = 100MB)
go
use TestDB
go
-- Setting the default file group
ALTER DATABASE TestDB
MODIFY FILEGROUP DefaultFileGroup default
go
--Creating a demo table in the default file group
create table MyTable (i int not null) on DefaultFileGroup
go
--see that the table is created on the DefaultFileGroup
exec sp_help MyTable
--Adding a clustered index without speciying where it will be located
create clustered index cix_MyTable_i on MyTable (i) on [primary]
go
--The table is now on the primary data file and not on the default data file
sp_help MyTable
--cleanup
use master
go
drop database TestDB
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/