SQL Server:索引视图验证基表中的插入错误

Question

我认为SQL Server索引视图错误地验证了基表中的插入。

为了模拟，考虑以下

创建表:

CREATE TABLE [dbo].[table_e]
(
[id] [int] NOT NULL,
[module] [varchar](50) NULL,
[event] [varchar](50) NULL,
[params] [nvarchar](max) NULL,
CONSTRAINT [PK_table_e] 
PRIMARY KEY CLUSTERED ([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

填充表

INSERT INTO [dbo].[table_e] ([id], [module], [event], [params])
VALUES (1, 'ModuleB', 'EventT', N'[{"type":"AccountId","value":"AccountX"},{"type":"AccountId","value":"AccountZ"},{"type":"Balance","value":10},{"type":"Balance","value":10}]'),
(2, 'ModuleB', 'EventT', N'[{"type":"AccountId","value":"AccountY"},{"type":"AccountId","value":"AccountX"},{"type":"Balance","value":20}]'),
(3, 'ModuleP', 'EventA', N'[{"type":"AccountId","value":"AccountZ"},{"type":"AccountId","value":"AccountY"},{"type":"Hash","value":"SomeHash"}]')

检查是否一切正常

SELECT * 
FROM [dbo].[table_e] -- returning 3 out of 3

创建schem比比皆是视图

CREATE VIEW [dbo].[iv_test]
WITH SCHEMABINDING
AS
SELECT 
e.[id],
CAST(JSON_VALUE(e.[params], '$[0].value') AS CHAR(66)) AS [AccountAddress_From],
CAST(JSON_VALUE(e.[params], '$[1].value') AS CHAR(66)) AS [AccountAddress_To],
CAST(JSON_VALUE(e.[params], '$[2].value') AS DECIMAL (36)) AS [Amount_Transferred],
CAST(JSON_VALUE(e.[params], '$[3].value') AS DECIMAL (36)) AS [Amount_Fees]
FROM 
[dbo].[table_e] e 
WHERE 
e.[module] = 'ModuleB' AND e.[event] = 'EventT'
GO

检查是否一切正常

SELECT * 
FROM [dbo].[iv_test]     -- returning 2 out of 3

清空表

DELETE FROM [table_e] --3 rows affected

通过创建聚集索引实现视图:

CREATE UNIQUE CLUSTERED INDEX [PK_iv_test] 
ON [dbo].[iv_test]([id] ASC)
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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

现在尝试添加与之前相同的数据

INSERT INTO [dbo].[table_e] ([id], [module], [event], [params])
VALUES (1, 'ModuleB', 'EventT', N'[{"type":"AccountId","value":"AccountX"},{"type":"AccountId","value":"AccountZ"},{"type":"Balance","value":10},{"type":"Balance","value":10}]'),
(2, 'ModuleB', 'EventT', N'[{"type":"AccountId","value":"AccountY"},{"type":"AccountId","value":"AccountX"},{"type":"Balance","value":20}]'),
(3, 'ModuleP', 'EventA', N'[{"type":"AccountId","value":"AccountZ"},{"type":"AccountId","value":"AccountY"},{"type":"Hash","value":"SomeHash"}]')

结果:INSERT不工作。群集索引的创建阻塞了我的INSERT，出现以下错误:

Msg 8115, Level 16, State 6, Line 105
将nvarchar转换为数据类型numeric时发生算术溢出错误。语句已被终止。

这是怎么回事?

它看起来像where子句应用于索引视图，例如e.[module_id] = ' balance '和e.[event_id] = 'Transfer'没有应用。

因此，插入到基表中的所有数据都将根据视图[iv_test]进行检查，而在我看来，只有插入的数据符合视图[iv_test]中指定的WHERE条件，才应该根据视图[iv_test]中的格式进行检查。

有趣的是:下面的方法是有效的:

• 首先插入ID为1的记录&2,
• 创建聚集索引;
• 插入ID为3的记录;

这当然不能解决我的问题，但很明显，在创建集群索引后，索引视图上有一些活动的WITH NOCHECK约束。

欢迎大家畅所欲言

Answer 1

我有一个"解决方案";给你，但没有完整的解释。

在索引视图定义中，将最后两个cast更改为try_cast:

CREATE VIEW [dbo].[iv_test]
WITH SCHEMABINDING
AS
SELECT 
e.[id]
,CAST(JSON_VALUE(e.[params], '$[0].value') AS CHAR(66)) AS [AccountAddress_From]
,CAST(JSON_VALUE(e.[params], '$[1].value') AS CHAR(66)) AS [AccountAddress_To]
,TRY_CAST(JSON_VALUE(e.[params], '$[2].value') AS DECIMAL (36)) AS [Amount_Transferred]
,TRY_CAST(JSON_VALUE(e.[params], '$[3].value') AS DECIMAL (36)) AS [Amount_Fees]
FROM [dbo].[table_e] e 
WHERE e.[module] = 'ModuleB' AND e.[event] = 'EventT'
GO

我知道你在想什么:

但是视图定义上的where子句应该过滤掉cast失败的行，所以这些不应该物化，所以物化应该成功

是的，逻辑上我想这是对的…这就是为什么在插入第一行之后创建索引(这将愉快地转换为十进制)，然后添加第三行时可以工作的原因。

所以我猜要做的推论是，当为多行数据创建过滤索引时，引擎执行的操作顺序与将一行插入表时所做的操作顺序必须有所不同。具体来说，似乎引擎对数据和的视图输出进行评估，然后在决定将什么放入索引时应用谓词。

我敢打赌，说，保罗·怀特可以提供细节，到底发生了什么在被子