Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Duplicate Indices on tables

Status
Not open for further replies.

moonshadow

Programmer
Oct 9, 2001
181
GB
In SQL Server 7, is there any advantage in having the same index defined more than once on a particular table. We have an old database which has tables with the same indices defined more than once on each table. I presume if SQL has 2 identical indices on the same table it will only ever use one of them in a query. If this is the case, I can drop the duplicate indices - the main data entry table has over 30 indices defined on it.
If I can drop these duplicate keys, can anyone help me with a script to drop them automatically from each table. I was thinking, that if I can produce a table with 3 fields listing table name, index name and a list of the index fields, I could write a query similar to this:
select tablename, max(indexname) from tblTableIndices group by FieldList having count(*)>1

This would then give me a list of table & indexname which I could use to drop the duplicates.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top