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?

closed as unclear what you're asking by Paul White, Colin 't Hart, dezso, RolandoMySQLDBA, Kin Shah Jul 16 '15 at 15:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Can you add some ddl and sample data (you can use sqlfiddle.com and select sql server RDBMS) ? SQL Server will use filtered index only if it thinks it benefits the query. There are some limitations - see here and here. – Kin Shah Jul 15 '15 at 21:29
  • 1
    How were you able to add an index on filename? I didn't think SQL Server supported indexes on nvarchar(MAX) columns. – Dan Guzman Jul 16 '15 at 1:40
  • you can have indexes on nvarchar(max) – RayofCommand Jul 16 '15 at 7:31
  • @RayofCommand: Not in SQL Server 2012 or lower, it seems. – Andriy M Jul 16 '15 at 11:11
  • 3
    The question is lacking precision and detail as already noted, but you should always be careful about data types. If the column is nvarchar, your constant literals should be prefixed with N e.g. >= N'ATT000'. Please do update your question to include at least table & index DDL, preferably some sample data and execution plans. – Paul White Jul 16 '15 at 11:20


WHERE FileName >= N'ATT000'
AND filename < N'ATT001'

And make sure you have included columns to avoid lookups.

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