sql server (alternate): Hi,

I am expanding our data warehouse solution with new filegroups on
several subsystems.

I want to know which idea is better!

- create clustered indexes on tables to 'move' them to new filegroups
- create these tables on the new filegroups.

The background of this question is as follows:

- we want the whole data on the new filegroups
- we want to know if there is any difference in performance between the
2 solutions

Thanks in advance,
Danny

Hey Danny,

In both cases your data will physically reside in the space where there
is either a clustered index OR (in the case of a heap) on the drive
where the table was created. What you didn't ask about was
nonclustered indexes; if you move the clustered index, the nonclustered
indexes will stay where they are. If the filegroups are one seperate
disk structures, you may see some disk i/o benefits.

Does that help?

Stu

Hi Stu,

Thanks. Nonclustered indexes are already on seperate filegroups. Do you
know wat the difference in locking / performance gain is if the table
structure stays on the 'old' filegroup (in case of an clustered index
to a new filegroup) or the table structure is on the new filegroup.

Danny

Where your clustered index is, your table is. When you move a
clustered index to a new filegroup, you are moving the entire table to
that filegroup, so there is no performance gain in moving the index
(unless you're moving to better hardware or a better configuration).

Just to be clear, in order to get a performance boost using filegroups,
those filegroups must be on seperate physical disk structures; however,
when you move a clustered index, you're moving the whole table.

HTH,
Stu

Dr Warehouse (d.riebeek@reddion.com) writes:
[quoted text, click to view]

The clustered index is the the table structure, so if you move the
clustered index, there will be nothing left on the old filegroup.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at