SQL Server 2016创建非聚集索引时语法错误问题求助

错误根源 #

你碰到的语法错误，核心问题是在WITH索引配置选项前加了分号——分号在SQL里是语句结束标记，加了之后数据库会把前面的CREATE INDEX当成完整语句，后面的WITH (...)就成了无意义的独立代码片段，自然触发语法报错。另外部分索引的WHERE子句存在冗余括号，虽不影响功能，但可简化优化。

修正后的完整索引代码 #

--- FIFO库存通用索引
CREATE NONCLUSTERED INDEX [IX_Dave_General]
    ON [dbo].[ICIVAL]
     ( 
       [ITEMNO] ASC,
       [TRANSDATE] DESC,
       [TRANSTYPE] ASC
     )    
INCLUDE ( [QUANTITY], [TRANSCOST] )
WITH (
      PAD_INDEX = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      IGNORE_DUP_KEY = OFF,
      DROP_EXISTING = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]   
GO

--- 入库类交易数量过滤索引（TRANSTYPE=1为入库）
CREATE NONCLUSTERED INDEX [IX_Dave_Items]
    ON [dbo].[ICIVAL]
     (            
      [ITEMNO] ASC,
      [TRANSDATE] ASC
      )
INCLUDE ( [QUANTITY])
WHERE [TRANSTYPE] = 1 --- 可按需扩展为OR [TRANSTYPE] = 5（对应退货入库）
WITH (
      PAD_INDEX = OFF,
      STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF,
      IGNORE_DUP_KEY = OFF, 
      DROP_EXISTING = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]    
GO

--- 入库类交易成本过滤索引（TRANSTYPE=1为入库）
CREATE NONCLUSTERED INDEX [IX_Dave_Price]
   ON [dbo].[ICIVAL]
    (            
      [ITEMNO] ASC,
      [TRANSDATE] ASC
     )    
INCLUDE ( [TRANSCOST])
WHERE [TRANSTYPE] = 1
WITH (
      PAD_INDEX = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF, 
      IGNORE_DUP_KEY = OFF,
      DROP_EXISTING = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON,
      FILLFACTOR = 100
)
ON [PRIMARY]    
GO

--- 剩余库存计算CTE（原代码无语法错误，保留）
;WITH    
cteStockSum
  AS ( SELECT   [ITEMNO] ,
                SUM(CASE WHEN [TRANSTYPE] = 5 THEN 0-[QUANTITY]
                         ELSE [QUANTITY]
                    END) AS TotalStock
       FROM     [dbo].[ICIVAL]
       GROUP BY [ITEMNO]
     )
-- 后续CTE或查询逻辑可继续编写在此处

核心修改说明 #

• 移除所有WITH前的分号，让索引配置选项成为CREATE INDEX语句的一部分，避免语句被错误截断。
• 简化WHERE子句的冗余括号（比如(([TRANSTYPE] = 1))改为[TRANSTYPE] = 1），提升代码可读性。
• 调整WITH选项内的换行格式，优化代码排版（非必需，但便于维护）。

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