Use table hints with variable table

Question

I have this variable table

declare @mapping table (
    the_row int
    , Company smallint
    , Branch smallint 
    , Flag bit 
      index aa clustered (Company, Branch)
    , index bb nonclustered (Flag)
)

It seems that I cannot use table hints like this

select *
from @mapping mapping with(index=[aa])

or

select *
from @mapping mapping with(index=[bb])

It complains with

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Is another way to use different index depending on the use case?

Answer 1

Index table hint is not not allowed for table variables. In the official docs the information is misleading:

as it's not specify which kind of tables support it. A glimpse of this can be found in this great post comparing the @table and #temp tables.

Generally, table variables are good for smaller amounts of data. So, if you are working with thousands of rows you better read about the performance differences.

Also, using table hints should be something rare. The engine is smart enough most of times to decide which index to use.

Answer 2

table hints like WITH (INDEX = ...) aren't allowed on table variables

• optimizer has all information about table variable (# of rows | structure ...) - no need to hint any specific index

you can control index usage with your query design

• if you want optimizer to use clustered index (Company, Branch) - you might want to write

SELECT *
FROM @mapping
WHERE Company = @Company AND Branch = @Branch;

• if you want to use nonclustered index on Flag, try to filter that column

SELECT *
FROM @mapping
WHERE Flag = @Flag;

• if index hinting is important for your case - consider temporary table instead of table variable

CREATE TABLE #mapping (
    the_row int
    , Company smallint
    , Branch smallint 
    , Flag bit 
);

CREATE CLUSTERED INDEX aa ON #mapping (Company, Branch);
CREATE NONCLUSTERED INDEX bb ON #mapping (Flag);