Set FORCESEEK for only one of hinted indexes 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))
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&lt;>fiddle link

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&lt;>fiddle link

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




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.