Fliegroups and Primary Keys

I have a question with regads to placement of data files/indexes on
multiple filegroups. Here is the current scenario:

I have a database comprised of two filegroups - PRIMARY and INDEX. The
PRIMARY filegroup is comprised of two files, one residing on the R
drive and another residing on the O drive. The INDEX filegroup
consists of a file on the S drive. The transaction logs reside on the
T drive. The box itself has five individual drive slots, not RAID'ed
or mirrored. The tables are created in the PRIMARY filegroup, the
indexes in the INDEX group.

My question is this: is there any inherent benefit to create the
PRIMARY KEYS in a different filegroup? Currently, they are being
created in the PRIMARY group along with the actual data tables.

Thank you in adavance for any help.

Anthony RobinsonAnthony Robinson (ansonee@.yahoo.com) writes:
> This might seem kind of odd, but I'm going to answer my own question:
> no, you can't put a clustered (primary key) index on a different
> filegroup than the data table.
> The leaf level is the data, so moot point at best. If you wanted to move
> the data to another file or drive, then I could see where placing
> indexes in a different filegroup would get you there.
> Sorry to waste everyone's time...amazing what you can think of when
> you're playing Collapse!!!

Nah, the question is not really that meaningless. Not as it was posted.
To wit, there is nothing in this world which says that clustered index
of a table must be on the primary key. Sometimes the PK is the best
bet for the clustered indexes, but there are also many cases where it
is not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

FKs don't publish?

I am using SQL2005 SP2 and noticed that the foreign keys don't publish
to the subscribers. Is this normal?
Thanks,
Peter Cwik
Peter,
It depends. Does this help?
http://technet.microsoft.com/en-us/library/ms180843.aspx
RLF
"PeterCwik" <pjcwik@.gmail.com> wrote in message
news:da63593c-961a-49e6-a511-96d7d60130ec@.k8g2000hsf.googlegroups.com...
>I am using SQL2005 SP2 and noticed that the foreign keys don't publish
> to the subscribers. Is this normal?
> Thanks,
> Peter Cwik
|||On Jan 3, 9:09Xam, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> It depends. XDoes this help?http://technet.microsoft.com/en-us/library/ms180843.aspx
> RLF
> "PeterCwik" <pjc...@.gmail.com> wrote in message
> news:da63593c-961a-49e6-a511-96d7d60130ec@.k8g2000hsf.googlegroups.com...
>
>
> - Show quoted text -
Unfortunately, no. The foreign key is set to Yes for Enforce For
Replication.
|||you need to look at the article properties. There is an option to copy
foreign key constraints - make sure this is set to true.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PeterCwik" <pjcwik@.gmail.com> wrote in message
news:da63593c-961a-49e6-a511-96d7d60130ec@.k8g2000hsf.googlegroups.com...
>I am using SQL2005 SP2 and noticed that the foreign keys don't publish
> to the subscribers. Is this normal?
> Thanks,
> Peter Cwik

FK from other database

how to solve that problem?
i have 2 databases, in few tables from one database i need foreign keys from
other databases.Cant do it. You could try using triggers and stored procedures to check for
consistency but you can't use FK across databases.
MC
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:OjfFmHtAFHA.1300@.TK2MSFTNGP14.phx.gbl...
> how to solve that problem?
> i have 2 databases, in few tables from one database i need foreign keys
> from other databases.
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:
>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?
for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:

>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?
|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:

> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>

fk constraints question

any way to turn off all foreign keys in a database and then turn them
back on after a few update statements are run?for each table you'd have to
alter table mytable nocheck constraint all
This will turn off check and FK constraints on the table... you could
combine this with
sp_msforeachtable ( if I remember the name correctly )
or generate the script ie
select 'alter table ' + name + ' nocheck constraint all' from
sysobjects where type = 'u' and id > 100
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:416BC63E.F3CE4029@.dontemailme.com...
> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?|||You can use ALTER TABLE on a table that has foreign key
constraints to disable the constraint, e.g.
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
You can find more information and an example in books online
under ALTER TABLE.
-Sue
On Tue, 12 Oct 2004 06:55:42 -0500, ch <ch@.dontemailme.com>
wrote:

>any way to turn off all foreign keys in a database and then turn them
>back on after a few update statements are run?|||Hi,
SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
And later...
SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Hermilson Tinoco.
"ch" wrote:

> any way to turn off all foreign keys in a database and then turn them
> back on after a few update statements are run?
>