如何在SQL Server中为表类型创建筛选索引？SQL Server 2016新方法及报错解决

先来说说表值类型（Table-Valued Type，TVP）创建筛选索引的两种场景，以及你遇到的错误的解决办法：

一、SQL Server 2016之前的版本：在表变量上创建筛选索引 #

在2016之前，表值类型属于架构级对象，不支持直接在定义中创建筛选索引。你需要先定义好表类型，然后在使用该类型的表变量上创建筛选索引。

步骤示例： #

1. 先创建表值类型：

CREATE TYPE dbo.OrderStatusType AS TABLE
(
    OrderID INT,
    Status VARCHAR(30),
    TotalAmount DECIMAL(18,2)
);
GO

2. 在使用该类型的变量时创建筛选索引：

DECLARE @OrderTVP dbo.OrderStatusType;

-- 可选：先插入数据（创建索引前可以有数据，也可以为空）
INSERT INTO @OrderTVP (OrderID, Status, TotalAmount)
VALUES (101, 'Shipped', 99.99), (102, 'Pending', 49.99), (103, 'Shipped', 149.99);

-- 为表变量创建筛选索引，只包含Status为'Shipped'的行
CREATE NONCLUSTERED INDEX IX_OrderTVP_Shipped
ON @OrderTVP (OrderID)
WHERE Status = 'Shipped';

二、SQL Server 2016及以后：内联定义筛选索引（新方法） #

SQL Server 2016引入了对表值类型的内联索引支持，你可以直接在表类型的定义中声明筛选索引，无需等到使用表变量时再创建。这是2016新增的语法，也是更简洁的方式。

示例代码： #

CREATE TYPE dbo.OrderStatusType2016 AS TABLE
(
    OrderID INT,
    Status VARCHAR(30),
    TotalAmount DECIMAL(18,2),
    -- 直接在表类型定义中内联创建筛选索引
    INDEX IX_ShippedOrders NONCLUSTERED (OrderID) WHERE Status = 'Shipped'
);
GO

三、你遇到的错误原因及解决 #

你看到的错误 Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, a WITH keyword and parenthesis are now required. 通常有两种原因：

• 你在2016之前的版本中，试图在表值类型的定义里直接使用INDEX关键字（比如像2016那样内联定义），但旧版本不支持这种语法，所以报错。解决办法是改用上面第一种方法，在表变量上创建索引。
• 如果是在2016及以后版本报错，可能是你写的索引语法有误，比如遗漏了索引类型（如NONCLUSTERED），或者语法格式不对。确保内联索引的语法和上面示例一致。

另外要注意：表变量上的索引（包括筛选索引）在SQL Server 2014及以前，优化器可能不会充分利用，2016及以后对表变量的索引支持做了改进，性能更好。

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