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

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

Constraint question

I have 'Table1' with two fields:
- RecID
- POID
I want to allow records to be entered in Table1 where the same RecID and
POID can exist across multiple records in Table1, but I don't want to allow
a POID to exist in Table1 twice with different RecIDs.
This is OK
RecID | POID
1 | 100
1 | 100
This is not OK
RecID | POID
1 | 100
2 | 100
What's the best way to set this up?
Thanks,
CB1. add another table with constraints PK(RecID, POID ) and
UNIQUE(POID) or vice versa
2. add a FK to table1 referencing that another table (RecID, POID )
P.S. there could be a problem with your database design. you might want
to post your DDL|||On Mon, 3 Oct 2005 13:28:22 -0400, Chris Burgess wrote:
(snip)
>This is OK
>RecID | POID
>1 | 100
>1 | 100
Hi Chris,
No it's not. Unless you have more columns in the table, but omitted them
in this post.
But with this design, you've now got two exact duplicates. And SQL gives
you no way to address just one of them. Every change you wish to make to
one of these rows will be made to the other as well.
If the reality you are modeling has two entities with RecID 1 and POID 1
and you've got a way to distinguish them, then add an extra column to
store whatever distinguishes the two in your business. If you've got the
both of them, they are really indistiguishable, but you still need to
store the fact that there are two of them, add a columns to hold the
current number. That solves your constraint problem as well:
CREATE TABLE YourTable
(RecID int NOT NULL,
POID int NOT NULL,
NumberOfItems int NOT NULL DEFAULT 1,
PRIMARY KEY (POID),
CHECK (NumberOfItems >= 1)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.