Generate index on foreign key columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by tklimczak » Generate index on foreign key columns

Generate index on foreign key columns

Rate Topic

Display Mode

Topic Options

Author

Message

tklimczak

Posted Wednesday, January 30, 2008 2:16 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 21, 2008 7:09 AM
Points: 13, Visits: 50

Comments posted to this topic are about the item Generate index on foreign key columns

Post #449223

John Mitchell-245523

Posted Tuesday, March 04, 2008 2:09 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 4,418, Visits: 7,156

Does this work where the foreign key constraint consists of more than one column?

John

Post #463615

srienstr

Posted Tuesday, March 04, 2008 9:09 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:19 AM
Points: 210, Visits: 370

Which version of SQL Server is this written for? I've got 2000, and neither the table names nor column names match the system tables.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Post #463858

Mike Byrd

Posted Tuesday, March 04, 2008 12:32 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:02 AM
Points: 80, Visits: 341

There are times when an index is not needed for a foreign key. If the table is small (low number of rows) or the index column has little selectivity, then why add the overhead of an index? I wrote a similar script about a month ago, but found that the were many times the index just wasn't needed.

Mike Byrd

Post #464023

srienstr

Posted Tuesday, March 04, 2008 12:48 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:19 AM
Points: 210, Visits: 370

Mike Byrd (3/4/2008)

It can be run as just a select first to pull up a list for evaluation. A starting point like that can be useful when you're trying to tune an inherited database.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Post #464039

mickael.mottet

Posted Thursday, February 05, 2009 2:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:22 AM
Points: 7, Visits: 39

How can we modify this script to :

- Rename all PK indexes to "IX_[TableName];
- Rename all FK indexes to "IX_[TableName]_[ColumnName]";

Thanks

Post #650625

« Prev Topic | Next Topic »

Permissions