overlapping & Redundant indexes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

overlapping & Redundant indexes

Author

Topic

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2005-11-08 : 10:29:30

Hello,

I'm looking for a bit of clarity,

I am removing indexes from a database as I believe they are redundant, but just want to check with my x-perts first !

Here are a couple of examples

INDEX COLUMNS CLUSTERED
Table 1 - Index 1 - ItemFolderId No
Index 2 - ItemFolderId, ItemNo No

Should I be removing Index 1 or removing Index 2 and creating an Index on the ItemNo field ( does MSSQL use a combination of 2 indexes to create a covering index?)

INDEX COLUMNS
Table 2 - Index 1 OrderNo, PAgeNo, InvoiceRefNo Index 2 InvoiceRefNo
Index 3 OrderNo

(None are clustered)

Should I be Dropping indexes 2 & 3 (if these columns are referenced on their own, does MSSQL have any problem using the wider index 1 for just 1 column?) or keep them and removing columns orderNo and InvoiceRefNo from index 1, I guess this again comes down to if MSSQL can efficiently join sperate indexes to create a covering?

INDEX COLUMNS CLUSTERED
Table 3 - Index 1 Efolderid Yes
Index 2 RequestNo No
Index 3 Efolderid, CostCentre1 No

if the Efolderid column is in the clustered index can i remove it from index 3?

Cheers

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-08 : 11:54:01

Should I be removing Index 1 or removing Index 2 and creating an Index on the ItemNo field ( does MSSQL use a combination of 2 indexes to create a covering index?)

Probably remove ItemNo unless it is unique in which case both might be useful as the compount could b covering.

if the Efolderid column is in the clustered index can i remove it from index 3?
Not - it will not be used for searches on Efolderid in the clustered index for seeks as it is not the first field.

The answer to the middle question is a combination of the other two.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources