SQL Server表变量无法使用INDEX表提示，如何按需指定索引？

问题描述 #

定义了包含聚集索引和非聚集索引的表变量：

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

尝试通过表提示指定索引执行查询时，比如：

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

或

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

系统返回错误：

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.

需要找到能根据场景为该表变量指定不同索引的可行方法。

可行解决方案 #

1. 修正表提示的语法位置 #

SQL Server对表变量的表提示语法有特殊要求，需调整WITH子句的位置：

• 写法一：将WITH子句放在表变量名之后、别名之前

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

• 写法二：用括号包裹表变量后再添加表提示

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

2. 替换为临时表（推荐生产场景） #

临时表支持和普通表完全一致的表提示语法，若业务场景允许临时表的使用，可替换表变量为临时表：

-- 创建带索引的临时表
create table #mapping (
    the_row int
    , Company smallint
    , Branch smallint 
    , Flag bit 
      index aa clustered (Company, Branch)
    , index bb nonclustered (Flag)
)

-- 直接使用表提示指定索引
select *
from #mapping mapping with(index=[aa])

-- 业务完成后清理临时表
drop table #mapping

3. 启用跟踪标记（仅测试场景） #

开启跟踪标记9114可以让SQL Server对表变量的索引提示语法兼容普通表，但该标记为全局设置，不建议生产环境使用，仅适合测试验证：

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

内容的提问来源于stack exchange，提问作者clickit