Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate index on foreign key columns Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 2:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, March 4, 2008 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,421, Visits: 10,077
Does this work where the foreign key constraint consists of more than one column?

John
Post #463615
Posted Tuesday, March 4, 2008 9:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, Visits: 433
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
Posted Tuesday, March 4, 2008 12:32 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 81, Visits: 371
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
Posted Tuesday, March 4, 2008 12:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, Visits: 433
Mike Byrd (3/4/2008)
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.


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
Posted Thursday, February 5, 2009 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 2:03 AM
Points: 7, Visits: 44
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 »

Add to briefcase

Permissions Expand / Collapse