If there is only a small subset of rows with
Archive = 0, you could use a filtered index:
CREATE NONCLUSTERED INDEX IX_Archive_Filtered
ON [schema].[table] ([Date_A], [Date_B])
WHERE ([Archive] = 0)
Note: I've made the index on the two date fields only because I don't know of any other fields in your table - it might make more sense for this to be on a different field
Additional Note: always take care to include the filtering columns in the filtered index keys or included columns, so here for one example of why: Incorrect row estimation given with a filtered index
This keeps the index small (which means it uses less storage, and is quicker to read from), because only the rows where Archive is 0 are included in it.
If a filtered index won't work for you (and there are a number of scenarios where they don't work, see Filtered Indexes and Dynamic SQL for one example), you could try a normal nonclustered index on the archive field OR the date fields. You would want to create the index on whichever field is most selective - meaning which one will narrow down the results the most.
For instance, again if only a small subset of your rows have an Archive value of 0, you could create this index:
CREATE NONCLUSTERED INDEX IX_Archive_Includes
ON [schema].[table] ([Archive])
INCLUDE ([Date_A], [Date_B])
This will let your query seek to the correct rows (where Archive is 0), and then filter easily on Date_A and Date_B.
Additional comments about your query:
- you really shouldn't use
select * - enumerate each of the fields you actually need in your results to be more efficient, and allow more effective indexing
- you shouldn't use
nolock if the accuracy of the results is important