By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Indexes and File groups

P: n/a
Something strange.

I have a database(SQL2000) with two file group(on seperate physical
drives).
One is meant for table data[PRIMARY] and one for indexes [INDEX].

So i create a table on the [PRIMARY] file group, and fill in
data.

Next I build a clustered index on the table, on the [INDEX] filegroup.

Once the index is built, the database now indicates that the filegroup
for the table [INDEX]! and not [PRIMARY] as i originally set it up for!

My question it then: Has the table been moved or is this somehow an
error in SQL server?
I would really appreciate any thought anyone might have on this?

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Jens

A clustered index is the table (Well not quiet, but close enough). It
is impossible to have a clustered index on a different filegroup from
the data. You can build non-clustered on a seperate filegroup.

I suggest you rebuild your clustered index on your primary filegroup.
Regards

John

Jul 23 '05 #2

P: n/a
Aha! Solved some mysteries for me :-). Thank you very much.
I guess i didnt quite understand how clustered indexes worked.

Actually i have a bunch of tables with clustered indexes which
currently reside my file group for indexes. The good news is,if I
understand you correctly,
the if I simply rebuild the clustered index on my data file group
the table data will be moved back.

Jul 23 '05 #3

P: n/a
Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the
index for you.

Regards

John

Jul 23 '05 #4

P: n/a
Im planning to recreate the clustered index like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will alse cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)

jo************@yahoo.co.uk wrote:
Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the index for you.

Regards

John


Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.