Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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): http://stackoverflow.com/a/2126482/27535

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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