NC Index not being used 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 &gt;= 'ATT000'
AND filename &lt; '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?
Try:

WHERE FileName &gt;= N'ATT000'
AND filename &lt; N'ATT001'


And make sure you have included columns to avoid lookups.