FKY but no index

I am looking at various databases I am supporting which has FKY defined
but
not index on those columns. I believe when the child table (having FKY)
is joined with the parent table (having PKY), it will definitely result
in table lock of the child table.
I want to index all those columns which are part of FKY. Is there a
quick script (in stored procedure or TSQL) which can pinpoint all
those tables with the said columns.
TIA.IMHO the executing of some standard script on a production database should
be treated very carefully, if you want to create indexes on the columns you
should consider doing this manually to know what you´ve done. Ok, it´s kind
of work , but it will worth it (if you comment your work) if you want to
mantain this on your own.
Sorry for not suggesting es standard solution, but in my opinion this is
best practise.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:1115307235.398241.216360@.g14g2000cwa.googlegroups.com...
>I am looking at various databases I am supporting which has FKY defined
> but
> not index on those columns. I believe when the child table (having FKY)
> is joined with the parent table (having PKY), it will definitely result
> in table lock of the child table.
> I want to index all those columns which are part of FKY. Is there a
> quick script (in stored procedure or TSQL) which can pinpoint all
> those tables with the said columns.
> TIA.
>|||Jens S=FC=DFmeyer wrote:
> IMHO the executing of some standard script on a production database
should
> be treated very carefully, if you want to create indexes on the
columns you
> should consider doing this manually to know what you=B4ve done. Ok,
it=B4s kind
> of work , but it will worth it (if you comment your work) if you want
to
> mantain this on your own.
> Sorry for not suggesting es standard solution, but in my opinion this
is
> best practise.
> HTH, Jens Suessmeyer.
sorry I was not clear. I want a script to pinpoint tables which
has missing indexes on the FKY columns. I don't want a script
to automatically build the index. I agree with you fully that
it has to be done with caution.

FKY but no index

I am looking at various databases I am supporting which has FKY defined
but
not index on those columns. I believe when the child table (having FKY)
is joined with the parent table (having PKY), it will definitely result
in table lock of the child table.
I want to index all those columns which are part of FKY. Is there a
quick script (in stored procedure or TSQL) which can pinpoint all
those tables with the said columns.
TIA.
IMHO the executing of some standard script on a production database should
be treated very carefully, if you want to create indexes on the columns you
should consider doing this manually to know what youve done. Ok, its kind
of work , but it will worth it (if you comment your work) if you want to
mantain this on your own.
Sorry for not suggesting es standard solution, but in my opinion this is
best practise.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:1115307235.398241.216360@.g14g2000cwa.googlegr oups.com...
>I am looking at various databases I am supporting which has FKY defined
> but
> not index on those columns. I believe when the child table (having FKY)
> is joined with the parent table (having PKY), it will definitely result
> in table lock of the child table.
> I want to index all those columns which are part of FKY. Is there a
> quick script (in stored procedure or TSQL) which can pinpoint all
> those tables with the said columns.
> TIA.
>
|||Jens S=FC=DFmeyer wrote:
> IMHO the executing of some standard script on a production database
should
> be treated very carefully, if you want to create indexes on the
columns you
> should consider doing this manually to know what you=B4ve done. Ok,
it=B4s kind
> of work , but it will worth it (if you comment your work) if you want
to
> mantain this on your own.
> Sorry for not suggesting es standard solution, but in my opinion this
is
> best practise.
> HTH, Jens Suessmeyer.
sorry I was not clear. I want a script to pinpoint tables which
has missing indexes on the FKY columns. I don't want a script
to automatically build the index. I agree with you fully that
it has to be done with caution.

FKY but no index

I am looking at various databases I am supporting which has FKY defined
but
not index on those columns. I believe when the child table (having FKY)
is joined with the parent table (having PKY), it will definitely result
in table lock of the child table.
I want to index all those columns which are part of FKY. Is there a
quick script (in stored procedure or TSQL) which can pinpoint all
those tables with the said columns.
TIA.IMHO the executing of some standard script on a production database should
be treated very carefully, if you want to create indexes on the columns you
should consider doing this manually to know what youve done. Ok, its kind
of work , but it will worth it (if you comment your work) if you want to
mantain this on your own.
Sorry for not suggesting es standard solution, but in my opinion this is
best practise.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:1115307235.398241.216360@.g14g2000cwa.googlegroups.com...
>I am looking at various databases I am supporting which has FKY defined
> but
> not index on those columns. I believe when the child table (having FKY)
> is joined with the parent table (having PKY), it will definitely result
> in table lock of the child table.
> I want to index all those columns which are part of FKY. Is there a
> quick script (in stored procedure or TSQL) which can pinpoint all
> those tables with the said columns.
> TIA.
>|||Jens S=FC=DFmeyer wrote:
> IMHO the executing of some standard script on a production database
should
> be treated very carefully, if you want to create indexes on the
columns you
> should consider doing this manually to know what you=B4ve done. Ok,
it=B4s kind
> of work , but it will worth it (if you comment your work) if you want
to
> mantain this on your own.
> Sorry for not suggesting es standard solution, but in my opinion this
is
> best practise.
> HTH, Jens Suessmeyer.
sorry I was not clear. I want a script to pinpoint tables which
has missing indexes on the FKY columns. I don't want a script
to automatically build the index. I agree with you fully that
it has to be done with caution.sql

FK on a column to a table where the column is not the PK but a Unique Index

Hello everyone, I am new to ERWIN and I need helps from the experts out
there.

We are using ERWin 4.1.2771 and have reversed engineered some MS SQL
Server 2000 databases.

The problem we are having is that we have a FK on a column to a table
where the PK of the referencing table is on another column (such as an
identity column). We have a unique index on the column in the PK table
and SQL Server allows you to build a FK reference even though the
column is not defined as the PK.

Does anyone know how to create this type of FK within Erwin?

Thank YouStandard SQL allows you to reference any UNIQUE() constraint from a
FOREIGN KEY. Indexes do not exist in Standard SQL, so be sure to use
the UNIQUE() constraint.|||Use a unique constraint rather than a unique index.

--
David Portas
SQL Server MVP
--

FK Index Advice

I have a couple custom database that I am building. They will be 3NF with several many to many linking tables.

In general, both databases will have about 30 tables. MOST tables will have an autonumber PK, and two FK fields related to PK's on another table. My question is about indexing...

I will not be designing the application(s) using the DB's (as far as ASP code, VB script) etc, so I am not familiar with the SQL to be used, nor am I confident that my developers will run the code by me before the system is done.

Therefore, I am trying to create very general indexes on each table that will serve generic needs (my attempt at half-way optimizing the DB). So each table has a unique index on the PK, and each FK has it OWN non-unique index. Is this the correct way to do this? Or, should I have one index that covers both FK columns rather than two indexes? Any thoughts? Please provide some reasoning with any answers.

Thanks in advance!

Ryan HuntRun the CPU intensive statements through the Index Tuning Wizard (ITW) in order to get any index recommendations that may be necessary.

Review information from this http://www.sql-server-performance.com/mr_indexing.asp link on choosing indexes.

FK

I know that in the case of a primary key (non-clustered)
the index part of the foreign key must be stored
separately from the table. Is this true if you have a
foreign key on a table? Is there data stored externally
to the table for a foreign key?
Also: uou don't have to defrag or reindex a foreign key
do you?A foreign key is just a rule. SQL Server doesn't have to store anything
additional as you add the FK constraint. SQL Server will, however, make sure
that your data adheres to your rules, so a check is performed as you modify
data. Creating an index on the foreign key column can be very beneficial to
assist for this validation. Not to mention that you often perform joins over
primary key - foreign key relationships. And remember that a FK column can
be a good candidate for the clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"GXX" <anonymous@.discussions.microsoft.com> wrote in message
news:442201c4020f$c3ca8150$a601280a@.phx.gbl...
> I know that in the case of a primary key (non-clustered)
> the index part of the foreign key must be stored
> separately from the table. Is this true if you have a
> foreign key on a table? Is there data stored externally
> to the table for a foreign key?
> Also: uou don't have to defrag or reindex a foreign key
> do you?

FK

I know that in the case of a primary key (non-clustered)
the index part of the foreign key must be stored
separately from the table. Is this true if you have a
foreign key on a table? Is there data stored externally
to the table for a foreign key?
Also: uou don't have to defrag or reindex a foreign key
do you?A foreign key is just a rule. SQL Server doesn't have to store anything
additional as you add the FK constraint. SQL Server will, however, make sure
that your data adheres to your rules, so a check is performed as you modify
data. Creating an index on the foreign key column can be very beneficial to
assist for this validation. Not to mention that you often perform joins over
primary key - foreign key relationships. And remember that a FK column can
be a good candidate for the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"GXX" <anonymous@.discussions.microsoft.com> wrote in message
news:442201c4020f$c3ca8150$a601280a@.phx.gbl...
> I know that in the case of a primary key (non-clustered)
> the index part of the foreign key must be stored
> separately from the table. Is this true if you have a
> foreign key on a table? Is there data stored externally
> to the table for a foreign key?
> Also: uou don't have to defrag or reindex a foreign key
> do you?