Contents of FileGroups

All,
Is it possible for a table to exist in more than 1 filegroup? Is it
possible to move all existing instances of that table to just 1
filegroup? . Using the script below:
SELECT DISTINCT (SELECT groupname
FROM sysfilegroups
WHERE groupid = a.groupid)
AS filegroup, OBJECT_NAME(id) AS 'object name'
FROM sysindexes a
WHERE (groupid <> 0)
ORDER BY filegroup, 'object name' ASC
I was about to see which filegroup a table resides in. I know that
where a clustered index exists so to must the table data must follow.
But, while running the above script, I have instances where some tables
exists in two filegroups. Using sp_help, I confirmed that the clustered
index and all other non-clustered indexes reside in FileGroup1. However
running the above script shows that part of the table also resides in
FileGroup2. This only occurs for about 5% of all tables (about 3000).
What would cause such an event? How can one rectify the situation by
merging the table instance on FileGroup2 into FileGroup1 and hopefully
removing this table instance in FileGroup2?
Thanks,
Ian in SDTMK a table can only exist in one filegroup. The filegroup may consist of
multiple files and extents from multiple files may be allocated to the
table. The clustered index actually is the table so creating the clustered
index ON a filegroup will move the table to a different filegroup.
HTH
Jerry
<theredmiata@.hotmail.com> wrote in message
news:1147992470.409194.55030@.i39g2000cwa.googlegroups.com...
> All,
> Is it possible for a table to exist in more than 1 filegroup? Is it
> possible to move all existing instances of that table to just 1
> filegroup? . Using the script below:
> SELECT DISTINCT (SELECT groupname
> FROM sysfilegroups
> WHERE groupid = a.groupid)
> AS filegroup, OBJECT_NAME(id) AS 'object name'
> FROM sysindexes a
> WHERE (groupid <> 0)
> ORDER BY filegroup, 'object name' ASC
> I was about to see which filegroup a table resides in. I know that
> where a clustered index exists so to must the table data must follow.
> But, while running the above script, I have instances where some tables
> exists in two filegroups. Using sp_help, I confirmed that the clustered
> index and all other non-clustered indexes reside in FileGroup1. However
> running the above script shows that part of the table also resides in
> FileGroup2. This only occurs for about 5% of all tables (about 3000).
> What would cause such an event? How can one rectify the situation by
> merging the table instance on FileGroup2 into FileGroup1 and hopefully
> removing this table instance in FileGroup2?
> Thanks,
> Ian in SD
>|||In SQL 2000 a table can only exist inside of 1 FileGroup, but you can create
non-clustered indexes on other filegroups.
In SQL 2005 you can use a partioning scheme to partition a table or an index
across multiple filegroups.
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||TMK a table can only exist in one filegroup. The filegroup may consist of
multiple files and extents from multiple files may be allocated to the
table. The clustered index actually is the table so creating the clustered
index ON a filegroup will move the table to a different filegroup.
HTH
Jerry
<theredmiata@.hotmail.com> wrote in message
news:1147992470.409194.55030@.i39g2000cwa.googlegroups.com...
> All,
> Is it possible for a table to exist in more than 1 filegroup? Is it
> possible to move all existing instances of that table to just 1
> filegroup? . Using the script below:
> SELECT DISTINCT (SELECT groupname
> FROM sysfilegroups
> WHERE groupid = a.groupid)
> AS filegroup, OBJECT_NAME(id) AS 'object name'
> FROM sysindexes a
> WHERE (groupid <> 0)
> ORDER BY filegroup, 'object name' ASC
> I was about to see which filegroup a table resides in. I know that
> where a clustered index exists so to must the table data must follow.
> But, while running the above script, I have instances where some tables
> exists in two filegroups. Using sp_help, I confirmed that the clustered
> index and all other non-clustered indexes reside in FileGroup1. However
> running the above script shows that part of the table also resides in
> FileGroup2. This only occurs for about 5% of all tables (about 3000).
> What would cause such an event? How can one rectify the situation by
> merging the table instance on FileGroup2 into FileGroup1 and hopefully
> removing this table instance in FileGroup2?
> Thanks,
> Ian in SD
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OzNZa4seGHA.4304@.TK2MSFTNGP05.phx.gbl...
> TMK a table can only exist in one filegroup. The filegroup may consist of
> multiple files and extents from multiple files may be allocated to the
> table. The clustered index actually is the table so creating the
> clustered index ON a filegroup will move the table to a different
> filegroup.
>
There are three physical parts to a table, and they can be on seperate file
groups in SQL 2000.
Clustered Index or Page Heap
Non-Clustered Indexes
text, ntext and image data
David