luckysql.kinda (9/14/2009)

---

Adi here is one twist

Adi Cohn (9/14/2009)

---

This is done on purpose. When you create a clustered index on a heap you might want to move the table to a different file group, but you might want to keep the table on the same file group that it was created on. The table and clustered index have to be on the same file group, but when you create the clustered index if you won’t specify it’s location, it will be created on the same file group as the table. If you will specify a specific file group, it will be created on that file group and the table will be moved to this file group. The only difference when you create a clustered index or a nonclustered index, is that the table might be moved, but this has nothing to do with the way that the server “decides” on which file group the index will be created. At both cases (creating clustered and nonclustered index) the way this “decision” is dictated to the server by the user’s input.

Adi

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.

--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