moonshadow
Programmer
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.
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.