Is there a way to use a `TABLE HINT` with two indexes on the same table and set the `FORCESEEK` for only one of them?

The following statement causes an error because the first index is columnstore:

    OPTION (TABLE HINT(ta, NOLOCK, INDEX (csi_index, PK_index), FORCESEEK))
Top Answer
Josh Darnell (imported from SE)
I think the answer to your question is no, at least I haven't found a way to do it.

It would be interesting to know what you hope to accomplish with this set of hints, specifically as a query hint (rather than normal inline table hints).

Here's a repro of your scenario:

```
DROP TABLE IF EXISTS dbo.Hintable;
CREATE TABLE dbo.Hintable
(
	Id int NOT NULL,
	Rando int NOT NULL,
	INDEX CSI_Index CLUSTERED COLUMNSTORE,
	CONSTRAINT PK_Index PRIMARY KEY NONCLUSTERED (Id)
);

INSERT INTO dbo.Hintable
	(Id, Rando)
SELECT
	v1.number,
	MAX(v1.[high])
FROM master.dbo.spt_values v1
WHERE v1.[high] IS NOT NULL
GROUP BY [number];

SELECT *
FROM dbo.Hintable ta
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index), FORCESEEK));
```

[db<>fiddle link][1]

That results in this error, which is different from the one you reported:

> Msg 8722 Level 16 State 1 Line 20  
> Cannot execute query. Semantic affecting hint 'nolock' appears in the 'TABLE HINT' clause of object 'ta' but not in the corresponding 'WITH' clause.  Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints match the WITH clause.

That's a documented limitation, and is easy to resolve by removing `NOLOCK` or adding it to an actual table hint:

```
SELECT *
FROM dbo.Hintable ta WITH (NOLOCK)
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index), FORCESEEK));
```

Now we get the error you implied (but didn't include specifically in your question):

> Msg 366 Level 16 State 1 Line 19  
> The query processor could not produce a query plan because the FORCESEEK hint on table or view 'Hintable' cannot be used with the column store index 'CSI_Index'.

There is a way to write the query that compiles with a combination of query and table hints:

```
SELECT *
FROM dbo.Hintable ta WITH (NOLOCK, INDEX (PK_Index), FORCESEEK)
WHERE Id = 500
OPTION (TABLE HINT(ta, NOLOCK, INDEX (CSI_Index, PK_Index)));
```

[db<>fiddle link][2]

The execution plan is kind of odd looking, in that it includes a seemingly-pointless [index intersection][3]:

[![screenshot of execution plan][4]][4]

- Both the scan and the seek filter on `Id = 500` 
- the scan outputs `Id`, `Rando`, and the columnstore row locator
- the seek just outputs the row locator 
- and then they join on the row locator

The scan can satisfy the query without needing the seek or join. The join doesn't filter out any rows (since it is on the row locator of the base table). The optimizer is honoring the requirement to use both indexes in the only way it can.

  [1]: https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=b3784080cf8c33ae8e6a2be61b938924
  [2]: https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=08a2baccd4b833e82d68eae22c763f67
  [3]: https://www.brentozar.com/archive/2016/06/lets-make-match-index-intersection/
  [4]: https://i.stack.imgur.com/W9gAn.png

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.