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<>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