1

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?

clickit
  • 21
  • 3
  • 1
    It's an interesting question, although really if have enough data that an index is helpful I think you'd be better of with a temp table which I imagine will perform better. – Dale K May 31 '23 at 05:47
  • Btw do you have a comma before your first index definition? – Dale K May 31 '23 at 05:52
  • 2
    The [official documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16) suggests it's not possible to specify the index for a table variable. – Dale K May 31 '23 at 05:53
  • 2
    echoing the answer and comment: if you have a performance issue with table variables, the first step is to move to temp tables. If you have a performance issue with _anything_, the _last_ step is to use an index hint. – Nick.Mc May 31 '23 at 06:03
  • 1
    Agreed, you should probably investigate why you think you need the hint in the first place, it's rare to *need* it without there being a better option – Charlieface May 31 '23 at 09:38

2 Answers2

3

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

enter image description here

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.

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • The docs I referenced in the comments do seem to be saying that it's not possible – Dale K May 31 '23 at 05:58
  • @DaleK I still can't find it. Could you share link to the specific section? – gotqn May 31 '23 at 06:12
  • 2
    @gotqn its the fact that only `table_or_view_name` is followed by an option for `with`, `@variable` doesn't have that option following it. – Dale K May 31 '23 at 06:32
0

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);
Lemonina
  • 712
  • 2
  • 14