Newbie Question About Indexes and Filegroups

Gang:
I have two hard-disk drives, one which I want to use to store
data and the other I want to store the indexes. I set up two
filegroups, one pointing to the first drive and the other pointing
to the second drive. In my CREATE TABLE statements, I use the ON
filegroup clause to point to the first filegroup (the "data"
filegroup).
In my CREATE INDEX statements, I use the ON filegroup clause to point
to the second filegroup (the "index" filegroup).
So far so good.
But! In the MSDN for "CREATE INDEX", there is this sentence: "Because
the leaf level of a clustered index and its data pages are the same
by definition, creating a clustered index and using the ON filegroup
clause EFFECTIVELY MOVES a table from the file on which the table was
created
to the new filegroup."
But I DON'T want my data moved from the "data" filegroup (the first
disk-drive) to the "index" filegroup (the second) disk-drive! Why?
What if I have to create a non-clustered index later on? That new
index will be located on the "index" filegroup, but because the data
was EFFECTIVELY MOVED due to the clustered index to the "index"
filegroup, I now have the data AND the non-
clustered index located on the SAME hard-drive (the "index" drive).
But, the whole idea is to have the data on one drive and the indexes
on another drive.
I realize that I can probably get away with have no clustered indexes
at
all by using the NONCLUSTERED keyword when creating PRIMARY KEYs and
when making indexes using CREATE INDEX.
Have I mis-understood something here'
Thanks,
ScottSince u already know that clustered index is basically
table data and non-clustered indexes are actual indexes so
it will be better if u store all ur clustered indexes on 1
filegroup( 1 drive) and all non-clustered indexes on 2nd
filegroup......Otherwise if u really want to use 2 hard-
disks effectively....place all ur data and indexes on 1st
hard disk and log files on 2nd hard disk....
Thanks
--Harvinder
>--Original Message--
>Gang:
>I have two hard-disk drives, one which I want to use to
store
>data and the other I want to store the indexes. I set up
two
>filegroups, one pointing to the first drive and the other
pointing
>to the second drive. In my CREATE TABLE statements, I
use the ON
>filegroup clause to point to the first filegroup
(the "data"
>filegroup).
>In my CREATE INDEX statements, I use the ON filegroup
clause to point
>to the second filegroup (the "index" filegroup).
>So far so good.
>But! In the MSDN for "CREATE INDEX", there is this
sentence: "Because
>the leaf level of a clustered index and its data pages
are the same
>by definition, creating a clustered index and using the
ON filegroup
>clause EFFECTIVELY MOVES a table from the file on which
the table was
>created
>to the new filegroup."
>But I DON'T want my data moved from the "data" filegroup
(the first
>disk-drive) to the "index" filegroup (the second) disk-
drive! Why?
>What if I have to create a non-clustered index later on?
That new
>index will be located on the "index" filegroup, but
because the data
>was EFFECTIVELY MOVED due to the clustered index to
the "index"
>filegroup, I now have the data AND the non-
>clustered index located on the SAME hard-drive
(the "index" drive).
>But, the whole idea is to have the data on one drive and
the indexes
>on another drive.
>I realize that I can probably get away with have no
clustered indexes
>at
>all by using the NONCLUSTERED keyword when creating
PRIMARY KEYs and
>when making indexes using CREATE INDEX.
>Have I mis-understood something here'
>Thanks,
>Scott
>.
>|||The data follows the clustered index. Period. You can create other =indexes on separate drives/file groups. In my opinion, I would be more =concerned about data availability (RAID) than I would about having =indexes on one drive and data on another. You don't go into much detail =about your machine, or the other hardware in the box, but I would be =tempted to set up a RAID array with the drives and put the data (and =indexes there). Don't worry about trying to separate them. This way =you will be able to loose a hard drive (it does happen) and you will be =able to continue working.
-- Keith, SQL Server MVP
"Scott" <slhecht@.attglobal.net> wrote in message =news:a018a20c.0307170621.756b5741@.posting.google.com...
> Gang:
> > I have two hard-disk drives, one which I want to use to store
> data and the other I want to store the indexes. I set up two
> filegroups, one pointing to the first drive and the other pointing
> to the second drive. In my CREATE TABLE statements, I use the ON
> filegroup clause to point to the first filegroup (the "data"
> filegroup).
> In my CREATE INDEX statements, I use the ON filegroup clause to point
> to the second filegroup (the "index" filegroup).
> > So far so good.
> > But! In the MSDN for "CREATE INDEX", there is this sentence: "Because
> the leaf level of a clustered index and its data pages are the same
> by definition, creating a clustered index and using the ON filegroup
> clause EFFECTIVELY MOVES a table from the file on which the table was
> created
> to the new filegroup."
> > But I DON'T want my data moved from the "data" filegroup (the first
> disk-drive) to the "index" filegroup (the second) disk-drive! Why? > What if I have to create a non-clustered index later on? That new
> index will be located on the "index" filegroup, but because the data
> was EFFECTIVELY MOVED due to the clustered index to the "index"
> filegroup, I now have the data AND the non-
> clustered index located on the SAME hard-drive (the "index" drive). > > But, the whole idea is to have the data on one drive and the indexes
> on another drive. > > I realize that I can probably get away with have no clustered indexes
> at
> all by using the NONCLUSTERED keyword when creating PRIMARY KEYs and > when making indexes using CREATE INDEX.
> > Have I mis-understood something here'
> > Thanks,
> Scott|||Hello,
The problem here is clustered indexes vrs non clustered
indexes. The nature of clustered indexes is they must be
saved in the same filegroup as the data, as they organise
the storage of the data.
An example of a clustered index is by default all primary
keys are clustered indexes.
Non clustered indexes however can be in a different
filegroup.
Peter
>--Original Message--
>Gang:
>I have two hard-disk drives, one which I want to use to
store
>data and the other I want to store the indexes. I set up
two
>filegroups, one pointing to the first drive and the other
pointing
>to the second drive. In my CREATE TABLE statements, I
use the ON
>filegroup clause to point to the first filegroup
(the "data"
>filegroup).
>In my CREATE INDEX statements, I use the ON filegroup
clause to point
>to the second filegroup (the "index" filegroup).
>So far so good.
>But! In the MSDN for "CREATE INDEX", there is this
sentence: "Because
>the leaf level of a clustered index and its data pages
are the same
>by definition, creating a clustered index and using the
ON filegroup
>clause EFFECTIVELY MOVES a table from the file on which
the table was
>created
>to the new filegroup."
>But I DON'T want my data moved from the "data" filegroup
(the first
>disk-drive) to the "index" filegroup (the second) disk-
drive! Why?
>What if I have to create a non-clustered index later on?
That new
>index will be located on the "index" filegroup, but
because the data
>was EFFECTIVELY MOVED due to the clustered index to
the "index"
>filegroup, I now have the data AND the non-
>clustered index located on the SAME hard-drive
(the "index" drive).
>But, the whole idea is to have the data on one drive and
the indexes
>on another drive.
>I realize that I can probably get away with have no
clustered indexes
>at
>all by using the NONCLUSTERED keyword when creating
PRIMARY KEYs and
>when making indexes using CREATE INDEX.
>Have I mis-understood something here'
>Thanks,
>Scott
>.
>|||I would agree completely with Keith. I see a LOT of customers spend a
tremendous amount of time worrying about how to partition thier data and
indexes across different file groups. Some people may disagree with me,
but... there are VERY few installations where this is ever necessary. I
might start to think about this if I had incredibly high IO rates or
hundreds of G (if not a full terrabyte) of data. Short of that... it just
won't make much of a noticeable difference and you could easily make things
worth.
Don't take this the wrong way, but... if you're asking questions like this,
then chances are that your SQL installation is small enough that you
wouldn't want to do this.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:eErOpGHTDHA.1920@.TK2MSFTNGP11.phx.gbl...
The data follows the clustered index. Period. You can create other indexes
on separate drives/file groups. In my opinion, I would be more concerned
about data availability (RAID) than I would about having indexes on one
drive and data on another. You don't go into much detail about your
machine, or the other hardware in the box, but I would be tempted to set up
a RAID array with the drives and put the data (and indexes there). Don't
worry about trying to separate them. This way you will be able to loose a
hard drive (it does happen) and you will be able to continue working.
--
Keith, SQL Server MVP
"Scott" <slhecht@.attglobal.net> wrote in message
news:a018a20c.0307170621.756b5741@.posting.google.com...
> Gang:
> I have two hard-disk drives, one which I want to use to store
> data and the other I want to store the indexes. I set up two
> filegroups, one pointing to the first drive and the other pointing
> to the second drive. In my CREATE TABLE statements, I use the ON
> filegroup clause to point to the first filegroup (the "data"
> filegroup).
> In my CREATE INDEX statements, I use the ON filegroup clause to point
> to the second filegroup (the "index" filegroup).
> So far so good.
> But! In the MSDN for "CREATE INDEX", there is this sentence: "Because
> the leaf level of a clustered index and its data pages are the same
> by definition, creating a clustered index and using the ON filegroup
> clause EFFECTIVELY MOVES a table from the file on which the table was
> created
> to the new filegroup."
> But I DON'T want my data moved from the "data" filegroup (the first
> disk-drive) to the "index" filegroup (the second) disk-drive! Why?
> What if I have to create a non-clustered index later on? That new
> index will be located on the "index" filegroup, but because the data
> was EFFECTIVELY MOVED due to the clustered index to the "index"
> filegroup, I now have the data AND the non-
> clustered index located on the SAME hard-drive (the "index" drive).
> But, the whole idea is to have the data on one drive and the indexes
> on another drive.
> I realize that I can probably get away with have no clustered indexes
> at
> all by using the NONCLUSTERED keyword when creating PRIMARY KEYs and
> when making indexes using CREATE INDEX.
> Have I mis-understood something here'
> Thanks,
> Scott