Create clustered index and move table

[timscronin]

I have a 3rd party app (can't change) which has some bad sql. I have a table that is used in the sql which if I put a clustered (I had an index on the fields in the sql but it would ignore and table scan) will use and stop doing table scan. this is a million row table that is growing. the data going in is pretty mich insert only. I have a separate array and file group which I have moved indexes to. 2 questions

1. If I would make a clustered index on the separate RAID and file group, doesn't the table need to go with it. I thought the clustered index and table had to be on same File Group

2. If I do this anyone see any issues with moving this table and index to the separate array/file group

 

[ptheriault]

If you move the clustered index to another filegroup it will move the table. The actual data for the table is stored at the leaf level of the clustered index.
Moving large tables tables to other file groups on there own disks can increase I/O so it's not a bad idea.



- Paul
- Database performance looks fine, it must be the Network!

 

[mrdenny]

Yes it will cause the table to move.

The vendor app will never know that the table has been moved.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

To add to the above, the only time you can do the index separately from the table is when you're using non-clustered indexes.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"