Unable to add a index hint to a simple query

Question

I have a table with milions of records

CREATE TABLE documents (  
 id bigint primary key,  
 external_system nvarchar(50),  
 external_docid nvarchar(50))  

and non clustered filtered unique index

CREATE UNIQUE INDEX documents_ix19 ON documents(external_system , external_docid ) 
WHERE external_system IS NOT NULL AND external_system <>'' AND external_docid IS NOT NULL AND external_docid <>'';

I have a simple query testing, if there is no duplicate document by a tuple (external_system, external_docid) to reject insertion request.

DECLARE @p0 nvarchar(50);
SET @p0 = N'invoicing';
DECLARE @p1 nvarchar(50);
SET @p1 = N'552eece13086103477897k';

SELECT 1
FROM documents 
WHERE external_system = @p0
AND external_docid = @p1

For somewhat reason, the Sql Server decided not to use the index documents_ix19 and chose to go through the clustered index.

I am not allowed to add a table hint. This query

DECLARE @p0 nvarchar(50);
SET @p0 = N'invoicing';
DECLARE @p1 nvarchar(50);
SET @p1 = N'552eece13086103477897k';

SELECT 1
FROM documents
WITH(INDEX(documents_ix19))
WHERE external_system = @p0
AND external_docid = @p1

fails with: Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN. This error I can also get on an empty table created at another database.

I found out that I can add at the end

OPTION(RECOMPILE)

But this doesn't seem to me as a good permanent solution.

Answer 1

Since you made the index filtered, SQL Server can only use the index if the conditions align with the index. But all you have is:

WHERE external_system = @p0
AND external_docid = @p1

Since SQL Server are compiling the query to put in the cache, it must select a plan that works for any value of @p0 and @p1, including the empty string and NULL.

There are two remedies. One is, as you have discovered, OPTION (RECOMPILE). And you are right, this is not the best solution. If nothing else, the query will blow up again, if you pass @p0 or @p1 as empty string or NULL.

The other solution is to add the conditions from the index to the query:

WHERE external_system = @p0
AND external_docid = @p1
AND external_system <> ''
AND external_system IS NOT NULL
AND external_docid <> ''
AND external_docid IS NOT NULL

Or you can just raise the white flag and make the index unfiltered...

Answer 2

You need the same where clause constraints in the query as the index. If either @p1 or @p2 are equal to ‘’ then the index would not work.