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.
| Author |
Topic |
|
chris_wood99
Yak Posting Veteran
70 Posts |
 Posted - 2005-11-22 : 06:04:46
|
| Just looking for some clarification as confusing myself a little! :-INDEX FIELDS1 CampaignFund_id - Clustered2 Campaign_id3 Fund_id4 Campaign_id, Fund_idI have several tables with this scenario, am I correct in assuming I should be removing index number 2 as index 4 can always cover this? Or should I remove number 4 and MSSQL with use a combination of 2 & 3 instead?cheers |
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-22 : 06:28:44
|
| I think if you are using both field in one query for i.e. Campaign_id, Fund_id and other 2 are not using then you can delete those.And if you are using Campaign_id and Fund_id in separate queries you can keep both of this and remove the 4th one if you are not using it.Surendra |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-22 : 06:50:59
|
| My understanding is:Assuming your queries explicitly do a WHERE or a JOIN on Campaign_id + Fund_id, OR you have a WHERE on Campaign_id and a SELECT on Fund_id (and optionally Campaign_id too) and NO other columns are referenced, then keep 4 and drop 2.Otherwise I reckon that 4 is not being used, so you could drop that and keep 2.2 & 3 are only useful if you have queries that explicitly use Campaign_id or Fund_id in the WHERE clause (or JOIN) AND if the data in those indexes is "selective" - i.e. there are NOT loads of entries for the same value [if so SQL will bypass the index, because it will be slower than doing a scan of the table]The other reason an index might be used is if you have NO where clause, but an ORDER BY e.g.:SELECT * FROM MyTable ORDER BY Campaign_id, Fund_idDunno if the Index Tuning Wizard would tell you what is used, or not?It would be handy to have a DEBUG option in SQL Server that incremented a COUNT on each index whenever it was used - then we could all just DROP the redundant ones after a week of use of the database!Kristen |
|
|
|
|
|
|
Index Removal