I have a table (9mm records) with a field called
filename nvarchar(max)(I didn't decide on this datatype and can't change it).
I'm wanting to count how many records meet the following criteria:
Where filename like 'ATT000%'. The results are only 8k records. I thought about adding a filtered index, but of course you can't do that with LIKE. So instead I went with
WHERE FileName >= 'ATT000' AND filename < 'ATT001'.
I receive the same record count. However, when I add that filtered index SQL refuses to use that index and still goes back to use the clustered index when I use that same criteria (copy and paste) from the index filter.
Its obviously WAY smaller and it works fine if I force the index hint. But I can't force it since this is a vendor application.
Any idea why I can't get SQL to use that index?