I've read the documentation on the MSDN, but it doesn't answer what I'm after. If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs and new IDs will insert into this index. That is all fine and dandy. But, in my WHERE clause, I'd like to specify a list of previous IDs and also include anything that is greater than the highest ID in the table e.g.

WHERE CampaignID IN (1,2,10,14,16,20,26)
  AND CampaignID > 26

Is this advisable? The goal I'm trying to achieve is to index all of the data that is presently used, and ignore all other data. The table is over 10Gb and it's in constant use so I can't remove these old records as it locks the table and stops data being inserted.


New IDs will always INSERT into the table, not the index. Your proposed INDEX would merely provide a more efficient lookup for some ID values.

Some thoughts though

The index may not be used too: it would have to cover all columns in the table. Or searches would have to return just a few rows so the key lookup needed (for a non-covering index). For more, see "Using Covering Indexes to Improve Query Performance".

As it stands, it may help, it may not.

Deleting or archiving old records can be made less painful too, which is probably the better solution. Example (SO): https://stackoverflow.com/a/2126482/27535

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.