sql:SQL Views and Materialized Views using sql server 2025
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
/**********************************************************************************************
SQL Server 2025 — 视图与物化视图（SQL Views and Materialized Views）
用视图与物化视图抽象查询逻辑、控制访问、提升读性能、管理数据新鲜度
国际化珠宝行业 ERP / CRM / HR 示例 · 万亿级高并发场景
────────────────────────────────────────────────────────────────────────────────────────────
目标实例：Microsoft SQL Server 2025 (RTM-GDR) 17.0.1135.8 X64 Enterprise Edition
目标数据库：JewelryAnalytics（兼容级别 170，RCSI ON）
────────────────────────────────────────────────────────────────────────────────────────────
【本机实测的 15 个坑】（全部在 SQL Server 2025 + JewelryAnalytics 上真实复现，
后文每一节都有对应的可运行演示与自检断言；括号内为实测数字，不是理论值）
坑 1 ★★★ SQL Server **没有** `CREATE MATERIALIZED VIEW` 语法。
本机实测：报错 **102**（"CREATE MATERIALIZED VIEW" 附近有语法错误）。
SQL Server 里「物化视图」的唯一内置等价物是 **索引视图（indexed view）**：
一个 WITH SCHEMABINDING 的视图 + 一个唯一的聚集索引。
不要指望 FROM 子句能写 MATERIALIZED VIEW，也不要指望别的数据库的语法能搬过来。
坑 2 ★★★ 优化器会**自动匹配（auto-match）**索引视图 —— 哪怕你查的是基表。
本机实测（rpt.OrgSalesFact，5,000,000 行列存，GROUP BY OrgUnitId）：
无索引视图存在时 → 表 "OrgSalesFact"，LOB 逻辑读取 1296，扫 7 个段
建了索引视图之后，**同一句 SQL 一字未改** → 表 "VW_OrgSalesAgg"，逻辑读取 4
324 倍的差距，而 SQL 文本、返回结果完全一致。
反面：你在三年前建过一个没人记得的索引视图，今天某一批基表查询的计划
就可能被它悄悄改写（写放大、行数估计、参数嗅探全都跟着变）。
坑 3 ★★★ 自动匹配是**脆弱的，而且失败时完全静默**。
本机实测（同一句基表聚合 SQL）：
全部 SET 选项正确 → 命中物化视图，逻辑读取 4
SET ANSI_WARNINGS OFF → **退化扫基表**，逻辑读取 1314 ✗ 无任何报错
SET CONCAT_NULL_YIELDS_NULL OFF → **退化扫基表**，逻辑读取 1314 ✗ 无任何报错
SET NUMERIC_ROUNDABORT ON → **退化扫基表**，逻辑读取 1314 ✗ 无任何报错
SET ARITHABORT OFF → 仍然命中物化视图（SQL Server 2025 已不再影响）
SET ANSI_NULLS OFF → 仍然命中物化视图
（1314 ≈ 基表 5,000,000 行列存的 1296 次 LOB 读取 + 少量开销；基线是 4）
注意最后两行：**网上流传的「ARITHABORT 一关就跑偏」在 SQL Server 2025 上已经
不再成立**（官方已将该 SET 选项废弃，行为等同常 ON）。真正要盯的是
ANSI_WARNINGS / CONCAT_NULL_YIELDS_NULL / NUMERIC_ROUNDABORT 这三兄弟 ——
而 ODBC 老驱动、部分 JDBC 客户端以及某些 ORM 的连接初始化脚本，恰恰会关掉它们，
或者把 NUMERIC_ROUNDABORT 打开。
坑 4 ★★★ 一旦加上 `WITH (NOEXPAND)`，失败就不再静默。
本机实测：ANSI_WARNINGS OFF + NOEXPAND → 报错 **1934**，
消息还会**点名**是哪个 SET 选项不对：
"SELECT 失败，因为下列 SET 选项的设置不正确: 'ANSI_WARNINGS'."
所以生产环境的报表 / 聚合查询，建议**总是显式写 NOEXPAND**：
要么正常走物化视图，要么立刻炸出来让你看见，而不是悄悄扫 500 万行。
坑 5 ★★ `NOEXPAND` 只能用在**真的有索引**的视图上。
本机实测：对普通视图用 NOEXPAND → 报错 **8171**「对象 'dbo.vw_SalesDetail' 上的提示 'noexpand' 无效」。
坑 6 ★★★ 索引视图的 15 类硬限制**在 CREATE VIEW 时不报错，到 CREATE INDEX 才炸**。
也就是说：你的视图能建成功，你以为万事大吉，然后给索引起名的那一刻才失败。
本机实测的 18 个场景（3 成功 / 15 报错）对应真实错误号：
COUNT(*) 替代 COUNT_BIG(*) → 10136
LEFT / RIGHT / FULL OUTER JOIN → 10113
UNION / INTERSECT / EXCEPT → 10116
SELECT DISTINCT → 10100
SELECT 列表里的标量子查询 → 10127
非确定性函数 GETDATE() → 1949
聚合 AVG() / MAX() / MIN() / STDEV → 10125
视图未 WITH SCHEMABINDING → 1939
视图定义里用了 CTE → 10137
视图里带表提示（NOLOCK 等） → 10140
视图自连接 → 1947
只建了「非唯一」聚集索引 → 1941
FROM 里用了派生表 → 10109
SUM 的参数是可空表达式 → 8662
反直觉的好消息：**视图建在 CLUSTERED COLUMNSTORE 基表之上是完全允许的**
（实测 5,000,000 行的 CCI 之上成功建出索引视图），
这是「列存 + 预聚合」组合的基础。
坑 7 ★★★ 视图里的窗口函数 / 聚合会**阻断谓词下推**。
本机实测（dbo.VI_Pushdown，500,000 行，谓词 WHERE Id = 250000 命中 1 行）：
直连基表 → 逻辑读取 3
透过普通视图 → 逻辑读取 3 （下推成功）
透过 TOP 100 PERCENT 视图 → 逻辑读取 3 （SQL Server 2025 已不再阻断）
透过 SELECT DISTINCT 视图 → 逻辑读取 3 （下推成功）
透过 ROW_NUMBER() 视图 → 逻辑读取 **2919** ★ 放大 973 倍
透过 SUM+GROUP BY 聚合视图 → 逻辑读取 **2919** ★ 同样全表
结论：给业务方看的「宽视图」里不要顺手塞 ROW_NUMBER 做行号；
要行号就在最外层 SELECT 里加，让优化器还能把外层谓词压下去。
另外注意：**「TOP 100 PERCENT 会屏蔽下推」这条老经验在 SQL Server 2025 上已经不成立**。
坑 8 ★★ 可更新视图没有 `WITH CHECK OPTION` 会**静默丢数据**。
本机实测（视图过滤 WHERE Status='Paid'，把一行的 Status 改成 'Returned'）：
无 CHECK OPTION → UPDATE 成功 1 行，但该行**从视图里彻底消失**
（视图可见行数 1 → 0），全程零报错、零警告
有 CHECK OPTION → 报错 **550**
「目标视图…指定了 WITH CHECK OPTION，而该操作的一个或多个结果行
又不符合 CHECK OPTION 约束」
这是「写完数据就查不到了」类工单的典型来源。
坑 9 ★★ `SELECT *` 视图的列清单是**创建时的快照**。
本机实测：视图列 = (a, b)；对基表 ADD COLUMN c；
视图既不报错也不更新，列清单仍是 (a, b)，查它也拿不到 c；
执行 `sp_refreshview 'dbo.VI_MetaView'` 之后列清单才变成 (a, b, c)。
基表改结构后，一定要批量刷新视图元数据，否则会得到「列不存在」或「少一列」的怪现象。
坑 10 ★★ 索引视图的维护是**同步**的，代价直接压在写入路径上。
本机实测（dbo.VI_Base，200,000 行，单行 UPDATE）：
无索引视图 → 逻辑读取 3
有聚合索引视图 → 逻辑读取 6（视图索引）+ 14（Worktable 聚合重算）+ 3（基表）
= 23，≈ 7.7 倍写放大
并且所有命中同一聚合组的并发写入都会去争用**同一行**物化记录
—— 这就是「读加速」的账单，必须在容量规划里算进去。
坑 11 ★★★ 索引视图**必须分区对齐**，否则会直接堵死万亿级的归档通道。
本机实测：
索视图的索引建在默认文件组（未分区）时
ALTER TABLE ... SWITCH PARTITION 2 TO ... →
报错 **11401**「表 'VI_Part' 为已分区，但索引视图 'VI_PartAgg' 的索引
'IXC_VI_PartAgg' 为未分区」
把同一个索引改写成 ON ps_vipart(Dt)（对齐到同一分区方案）
→ SWITCH PARTITION **成功**
几 TB 的冷数据出库靠的就是 SWITCH PARTITION（元数据操作，秒级），
一个没对齐的索引视图能把它变成几小时的 DELETE。
坑 12 ★ 即使对齐了，索引视图仍然阻塞 `TRUNCATE TABLE`。
本机实测：TRUNCATE TABLE dbo.VI_Part WITH (PARTITIONS (3)) → 报错 **3729**
「无法对 'dbo.VI_Part' 执行 TRUNCATE TABLE，因为对象 'VI_PartAgg' 正引用它」。
另外 `ALTER TABLE ... ALTER COLUMN` → 报错 **4922**
「由于一个或多个对象访问此列，ALTER TABLE ALTER COLUMN Amt 失败」。
SCHEMABINDING 是一把双刃剑：它保住了执行计划与元数据一致性，
同时把基表的 DDL 自由度锁死。
坑 13 ★ 视图嵌套**没有运行时开销**，成本只在编译期。
本机实测：三层嵌套视图（vw_Deep3 → vw_Deep2 → vw_Deep1）与等价的直接三表 JOIN，
读取次数**完全相同**（Product 4 / SalesOrderLine 74 / SalesOrder 2）。
所以「多层视图会不会拖慢查询」的答案是：不会。
真正会拖慢的是坑 7（窗口函数/聚合阻断下推）和坑 6（视图内 CTE 等让优化器失去选择）。
坑 14 ★ 视图是**访问控制边界**，这是它比 CTE 更重要的价值。
本机实测：给 v_reader 只授 `GRANT SELECT ON dbo.vw_SalesDetail`，
并显式 `DENY SELECT ON erp.SalesOrder`：
透过视图查询 → 成功，11,296 行（所有权链生效，不需要基表权限）
直接查基表 → 报错 **229**「拒绝了对对象 'SalesOrder' …的 SELECT 权限」
把「行/列级授权」做在视图上，是 ERP 里最便宜、最不容易漏的权限方案。
坑 15 ★ SQL Server 2025 的 `GENERATE_SERIES` **不支持列别名列表**。
本机实测：`FROM GENERATE_SERIES(1,10) AS gs(v)` → 报错 **195**
「'GENERATE_SERIES' 不是可以识别的 函数名称」；
必须直接用它的内置列名 `value`，或套一层派生表。
（造测试数据时会立刻撞上，故记在此处。）
────────────────────────────────────────────────────────────────────────────────────────────
章节：00 环境与元数据 / 01 抽象查询逻辑 / 02 访问控制 / 03 元数据新鲜度 /
04 可更新视图 / 05 谓词下推 / 06 物化视图等价物 / 07 索引视图限制 /
08 物化收益实测 / 09 SET 选项陷阱 / 10 写入放大与数据新鲜度 /
11 万亿级高并发 / 12 运行时诊断与决策清单 / 13 自检 / 14 总结
**********************************************************************************************/
PRINT '==============================================================================================';
PRINT ' SQL Server 2025 视图与物化视图 · 国际化珠宝行业 ERP/CRM/HR 演示';
PRINT '==============================================================================================';
/*==============================================================================================
00 环境、版本与既有对象盘点
==============================================================================================*/
PRINT '';
PRINT '### 00 环境与元数据 ###';
SELECT @@VERSION AS 版本信息;
SELECT 数据库 = DB_NAME(),
兼容级别 = DATABASEPROPERTYEX(DB_NAME(), 'CompatibilityLevel'),
RCSI快照读 = is_read_committed_snapshot_on,
快照隔离 = snapshot_isolation_state_desc,
排序规则 = CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS varchar(60))
FROM sys.databases WHERE name = DB_NAME();
PRINT '-- 00.1 本会话的 SET 选项（决定索引视图能否被自动匹配，见坑 3）--';
SELECT SESSIONPROPERTY('ANSI_NULLS') AS [ANSI_NULLS],
SESSIONPROPERTY('ANSI_PADDING') AS [ANSI_PADDING],
SESSIONPROPERTY('ANSI_WARNINGS') AS [ANSI_WARNINGS],
SESSIONPROPERTY('ARITHABORT') AS [ARITHABORT],
SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS [CONCAT_NULL_YIELDS_NULL],
SESSIONPROPERTY('NUMERIC_ROUNDABORT') AS [NUMERIC_ROUNDABORT],
SESSIONPROPERTY('QUOTED_IDENTIFIER') AS [QUOTED_IDENTIFIER];
PRINT '-- 00.2 本机是否支持 CREATE MATERIALIZED VIEW（坑 1）--';
BEGIN TRY
EXEC sp_executesql N'CREATE MATERIALIZED VIEW dbo.mv_probe AS SELECT 1 AS a;';
PRINT ' => 竟然支持（与预期不符）';
END TRY
BEGIN CATCH
PRINT ' => 错误 ' + CAST(ERROR_NUMBER() AS varchar(10)) + N' : ' + ERROR_MESSAGE();
PRINT ' => 结论：SQL Server 无此语法，「物化视图」只能靠索引视图实现。';
END CATCH
PRINT '-- 00.3 演示用基表清单与真实基数 --';
SELECT 表名 = s.name + '.' + t.name,
引擎 = MAX(i.type_desc),
近似行数 = SUM(p.rows)
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = p.index_id
WHERE (s.name + '.' + t.name) IN (
'rpt.OrgSalesFact', 'erp.SalesOrder', 'erp.SalesOrderLine', 'erp.Product',
'erp.Store', 'crm.Customer', 'crm.Interaction', 'hr.Employee', 'hr.OrgUnit',
'hr.Attendance', 'dbo.Country')
GROUP BY s.name + '.' + t.name
ORDER BY 近似行数 DESC;
PRINT '-- 00.4 rpt.OrgSalesFact 是列存：sys.partitions 的行数是错的，必须读行列组 DMV --';
SELECT 分区表报告行数 = (SELECT SUM(rows) FROM sys.partitions
WHERE object_id = OBJECT_ID('rpt.OrgSalesFact') AND index_id IN (0,1)),
行列组真实行数 = (SELECT SUM(total_rows) FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('rpt.OrgSalesFact')),
RowGroup数 = (SELECT COUNT(*) FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('rpt.OrgSalesFact'));
PRINT '-- 00.5 行业维度分布（珠宝业：7 国 / 21 品类）--';
SELECT 国家数 = (SELECT COUNT(*) FROM dbo.Country),
珠宝品类数 = (SELECT COUNT(*) FROM erp.ProductCategory),
门店数 = (SELECT COUNT(*) FROM erp.Store),
员工数 = (SELECT COUNT(*) FROM hr.Employee),
组织单元数 = (SELECT COUNT(*) FROM hr.OrgUnit),
会员数 = (SELECT COUNT(*) FROM crm.Customer);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
00-B 清理：保证脚本可重复执行（3 次运行结果一致）
────────────────────────────────────────────────────────────────────────────────────────────
说明：本脚本只清理「本篇自己创建的对象」（VI_ / vw_ / VW_ 前缀 + 专用分区方案 + 测试用户），
绝不触碰其它章节留下的演示对象，避免破坏既有环境。
==============================================================================================*/
PRINT '';
PRINT '### 00-B 清理并重建演示对象 ###';
DECLARE @drop nvarchar(max) = N'';
DECLARE @nm sysname, @sch sysname;
DECLARE curV CURSOR LOCAL FAST_FORWARD FOR
SELECT s.name, v.name FROM sys.views v JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE v.name IN ('vw_SalesDetail','vw_Customer360','VW_OrgSalesAgg','VI_BaseAgg','VI_PartAgg',
'vw_PD_Plain','vw_PD_Top','vw_PD_RowNum','vw_PD_Distinct','vw_PD_Agg',
'vw_SO_Checked','vw_SO_NoCheck','vw_SO_Update','vw_Deep1','vw_Deep2','vw_Deep3',
'VI_MetaView','VI_MetaView2',
'T_a','T_b','T_c','T_d','T_e','T_f','T_g','T_h','T_i','T_j',
'IX_01','IX_02','IX_03','IX_04','IX_05','IX_06','IX_07','IX_08','IX_09',
'IX_10','IX_11','IX_12','IX_13','IX_14','IX_15','IX_16','IX_17')
OR v.name LIKE 'VI[_]C[0-9][0-9]';
OPEN curV FETCH NEXT FROM curV INTO @sch, @nm;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @drop = @drop + N'DROP VIEW ' + QUOTENAME(@sch) + N'.' + QUOTENAME(@nm) + N';';
FETCH NEXT FROM curV INTO @sch, @nm;
END
CLOSE curV DEALLOCATE curV;
DECLARE curT CURSOR LOCAL FAST_FORWARD FOR
SELECT s.name, t.name FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name IN ('VI_Base','VI_Pushdown','VI_Part','VI_PartStage','VI_MetaBase',
'VI_ResultCheck','VI_RestrictionCheck');
OPEN curT FETCH NEXT FROM curT INTO @sch, @nm;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @drop = @drop + N'DROP TABLE ' + QUOTENAME(@sch) + N'.' + QUOTENAME(@nm) + N';';
FETCH NEXT FROM curT INTO @sch, @nm;
END
CLOSE curT DEALLOCATE curT;
IF OBJECT_ID('dbo.VI_MatchProbe', 'P') IS NOT NULL SET @drop = @drop + N'DROP PROCEDURE dbo.VI_MatchProbe;';
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'v_reader')
SET @drop = @drop + N'DROP USER v_reader;';
IF @drop <> N'' EXEC sp_executesql @drop;
PRINT ' 视图/表/过程/用户清理完成。';
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'ps_vipart')
EXEC('DROP PARTITION SCHEME ps_vipart;');
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'pf_vipart')
EXEC('DROP PARTITION FUNCTION pf_vipart;');
PRINT ' 分区方案清理完成。';
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
00-C 建立演示基座：珠宝行业 ERP/CRM/HR 事实与维度
────────────────────────────────────────────────────────────────────────────────────────────
VI_Base ≈ 门店×品类×日 的销售明细（对应 erp.SalesOrderLine 的量级放大版）
VI_Pushdown ≈ 需要按单号精确点查的订单明细
VI_Part ≈ 按日期分区的历史流水（对应万亿级归档场景）
==============================================================================================*/
PRINT '-- C1. dbo.VI_Base（200,000 行 门店品类销售明细）--';
CREATE TABLE dbo.VI_Base(
Id int NOT NULL CONSTRAINT PK_VI_Base PRIMARY KEY CLUSTERED,
OrgUnitId int NOT NULL, -- 组织单元：大区/城市/门店/柜组
CategoryId int NOT NULL, -- 珠宝品类：黄金/铂金/钻石/翡翠/腕表…
OrderDate date NOT NULL,
Qty int NOT NULL,
AmountUsd decimal(18,4) NOT NULL,
Note nvarchar(50) NULL
);
INSERT dbo.VI_Base(Id, OrgUnitId, CategoryId, OrderDate, Qty, AmountUsd, Note)
SELECT value,
(value % 40) + 1,
(value % 21) + 1,
DATEADD(day, -(value % 360), '2026-10-01'),
(value % 7) + 1,
((value % 991) + 1) * 1.25,
CASE WHEN value % 13 = 0 THEN NULL ELSE N'零售' END
FROM GENERATE_SERIES(1, 200000); -- 注意：不支持 AS gs(v) 列别名列表（坑 15）
DECLARE @nBase int = (SELECT COUNT(*) FROM dbo.VI_Base);
PRINT ' dbo.VI_Base 行数 = ' + CAST(@nBase AS varchar(20));
CREATE NONCLUSTERED INDEX IX_VI_Base_OrgCat ON dbo.VI_Base(OrgUnitId, CategoryId) INCLUDE(AmountUsd, Qty);
PRINT ' IX_VI_Base_OrgCat 已建。';
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- C2. dbo.VI_Pushdown（500,000 行 订单明细）--';
CREATE TABLE dbo.VI_Pushdown(
Id int NOT NULL CONSTRAINT PK_VI_Pushdown PRIMARY KEY CLUSTERED,
OrgUnitId int NOT NULL,
CategoryId int NOT NULL,
Dt date NOT NULL,
Amt decimal(18,4) NOT NULL
);
INSERT dbo.VI_Pushdown(Id, OrgUnitId, CategoryId, Dt, Amt)
SELECT value, (value % 10) + 1, (value % 21) + 1,
DATEADD(day, -(value % 300), '2026-10-01'), value * 0.37
FROM GENERATE_SERIES(1, 500000);
CREATE NONCLUSTERED INDEX IX_VI_Pushdown_Org ON dbo.VI_Pushdown(OrgUnitId);
DECLARE @nPush int = (SELECT COUNT(*) FROM dbo.VI_Pushdown);
PRINT ' dbo.VI_Pushdown 行数 = ' + CAST(@nPush AS varchar(20));
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- C3. 分区表 dbo.VI_Part / dbo.VI_PartStage（万亿级归档场景）--';
CREATE PARTITION FUNCTION pf_vipart(date) AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-07-01');
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
CREATE PARTITION SCHEME ps_vipart AS PARTITION pf_vipart ALL TO ([PRIMARY]);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
CREATE TABLE dbo.VI_Part(
Id int NOT NULL,
OrgUnitId int NOT NULL,
Amt decimal(18,4) NOT NULL,
Dt date NOT NULL
) ON ps_vipart(Dt);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
ALTER TABLE dbo.VI_Part ADD CONSTRAINT PK_VI_Part PRIMARY KEY CLUSTERED (Id, Dt) ON ps_vipart(Dt);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
INSERT dbo.VI_Part(Id, OrgUnitId, Amt, Dt)
SELECT value, (value % 10) + 1, value * 1.5, DATEADD(day, value % 400, '2025-06-01')
FROM GENERATE_SERIES(1, 5000);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
CREATE TABLE dbo.VI_PartStage(
Id int NOT NULL,
OrgUnitId int NOT NULL,
Amt decimal(18,4) NOT NULL,
Dt date NOT NULL
) ON ps_vipart(Dt);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
ALTER TABLE dbo.VI_PartStage ADD CONSTRAINT PK_VI_PartStage PRIMARY KEY CLUSTERED (Id, Dt) ON ps_vipart(Dt);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT ' dbo.VI_Part 分区分布：';
SELECT 分区号 = p.partition_number,
下边界 = ISNULL(CONVERT(varchar(20), prv.value, 23), N'(MIN)'),
行数 = p.rows
FROM sys.partitions p
LEFT JOIN sys.partition_range_values prv
ON prv.function_id = (SELECT function_id FROM sys.partition_functions WHERE name = 'pf_vipart')
AND prv.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID('dbo.VI_Part') AND p.index_id = 1
ORDER BY p.partition_number;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- C4. dbo.VI_MetaBase（视图元数据新鲜度演示）--';
CREATE TABLE dbo.VI_MetaBase(a int NOT NULL, b int NOT NULL);
INSERT dbo.VI_MetaBase VALUES (1, 2);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- C5. 结果校验表（供自检章节断言使用）--';
CREATE TABLE dbo.VI_ResultCheck(
检查项 varchar(60) NOT NULL CONSTRAINT PK_VI_ResultCheck PRIMARY KEY,
实测值 nvarchar(200) NULL,
期望 nvarchar(200) NULL,
是否通过 bit NULL
);
CREATE TABLE dbo.VI_RestrictionCheck(
seq int NOT NULL CONSTRAINT PK_VI_RestrictionCheck PRIMARY KEY,
场景 nvarchar(160) NOT NULL,
建视图错误号 int NULL,
建索引错误号 int NULL,
结论 varchar(20) NULL
);
PRINT ' 结果校验表已就绪。';
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
01 视图的第一价值：抽象查询逻辑（不回写业务逻辑、不产生运行时开销）
────────────────────────────────────────────────────────────────────────────────────────────
珠宝 ERP 的现实：一张「销售明细」要 JOIN 订单 + 订单行 + 商品 + 门店 + 客户 + 组织，
六个业务系统各自抄一份 JOIN 条件，于是同一口径出现六种实现。
视图把这个 JOIN 固化成一个「契约」：口径只有一处，变更只改一处。
本节要证明的是本机实测结论（坑 13）：
三层嵌套视图 vs 等价的直接三表 JOIN，**读取次数完全相同**。
视图是「宏」，不是「表」——运行时零额外开销，代价只在编译期展开。
度量方法说明：
下面建立 dbo.VI_Bench 通用度量器。它把被测 SQL 包进一个「无参存储过程」，
先预热一次、再正式执行一次，然后从 sys.dm_exec_query_stats 取
total_logical_reads / total_worker_time 的**增量**。
这样做的好处：每次执行都有独立的缓存条目，度量不会互相污染
（直接对着匿名 SQL 取 query_hash 会取到上一次不同 SET 选项的旧计划，本项目踩过）。
==============================================================================================*/
PRINT '';
PRINT '### 01 用视图抽象查询逻辑 ###';
PRINT '-- 01.1 建立通用度量器 dbo.VI_Bench --';
GO
CREATE OR ALTER PROCEDURE dbo.VI_Bench
@proc sysname,
@reads bigint OUTPUT,
@cpu_us bigint OUTPUT,
@elapsed_ms bigint OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @b_reads bigint = 0, @b_cpu bigint = 0, @b_el bigint = 0;
DECLARE @a_reads bigint = 0, @a_cpu bigint = 0, @a_el bigint = 0;
EXEC @proc; -- 预热，消除物理读与首次编译噪声
SELECT @b_reads = SUM(qs.total_logical_reads),
@b_cpu = SUM(qs.total_worker_time),
@b_el = SUM(qs.total_elapsed_time)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.objectid = OBJECT_ID(@proc);
EXEC @proc; -- 正式测量
SELECT @a_reads = SUM(qs.total_logical_reads),
@a_cpu = SUM(qs.total_worker_time),
@a_el = SUM(qs.total_elapsed_time)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.objectid = OBJECT_ID(@proc);
SET @reads = ISNULL(@a_reads, 0) - ISNULL(@b_reads, 0);
SET @cpu_us = ISNULL(@a_cpu, 0) - ISNULL(@b_cpu, 0);
SET @elapsed_ms = (ISNULL(@a_el, 0) - ISNULL(@b_el, 0)) / 1000;
END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- 01.2 建立面向业务的抽象视图（珠宝 ERP/CRM 口径契约）--';
-- 销售明细宽视图：订单 × 订单行 × 商品（口径集中在视图里）
GO
CREATE VIEW dbo.vw_SalesDetail AS
SELECT o.OrderId, o.OrderNo, o.OrderDate, o.CountryCode, o.StoreCode, o.Status,
o.NetAmountUsd,
ol.LineId, ol.ProductId, p.Sku, p.Category, p.MetalType, p.Gemstone,
ol.Quantity, ol.LineAmount
FROM erp.SalesOrder AS o
JOIN erp.SalesOrderLine AS ol ON ol.OrderId = o.OrderId
JOIN erp.Product AS p ON p.ProductId = ol.ProductId;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
-- 会员 360 视图：会员 × 互动（聚合口径固化，隐藏「LEFT JOIN + 可空聚合」的细节）
GO
CREATE VIEW dbo.vw_Customer360 AS
SELECT c.CustomerId, c.CustomerNo, c.CustomerName, c.CountryCode, c.Tier,
COUNT(i.InteractionId) AS InteractionCnt,
AVG(CAST(i.Satisfaction AS decimal(9,2))) AS AvgSatisfaction,
SUM(i.AmountSpent) AS SpentUsd
FROM crm.Customer AS c
LEFT JOIN crm.Interaction AS i ON i.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.CustomerNo, c.CustomerName, c.CountryCode, c.Tier;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
-- 三层嵌套：模拟「基础层 → 明细层 → 展现层」的企业级视图分层
GO
CREATE VIEW dbo.vw_Deep1 AS
SELECT OrderId, OrderDate, CountryCode, StoreCode, CustomerId, NetAmountUsd, Status
FROM erp.SalesOrder;
GO
GO
CREATE VIEW dbo.vw_Deep2 AS
SELECT a.OrderId, a.OrderDate, a.CountryCode, a.StoreCode, a.CustomerId, a.NetAmountUsd, a.Status,
b.LineId, b.ProductId, b.Quantity
FROM dbo.vw_Deep1 AS a
JOIN erp.SalesOrderLine AS b ON b.OrderId = a.OrderId;
GO
GO
CREATE VIEW dbo.vw_Deep3 AS
SELECT x.*, p.Sku, p.Category, p.MetalType
FROM dbo.vw_Deep2 AS x
JOIN erp.Product AS p ON p.ProductId = x.ProductId;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- 01.3 建立两个「等价」度量过程：直接三表 JOIN vs 三层嵌套视图 --';
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_Direct3 AS
BEGIN
SET NOCOUNT ON;
SELECT o.OrderId, o.OrderDate, o.CountryCode, o.StoreCode, o.CustomerId,
o.NetAmountUsd, o.Status, l.LineId, l.ProductId, l.Quantity,
p.Sku, p.Category, p.MetalType
FROM erp.SalesOrder AS o
JOIN erp.SalesOrderLine AS l ON l.OrderId = o.OrderId
JOIN erp.Product AS p ON p.ProductId = l.ProductId
WHERE o.OrderId = (SELECT MIN(OrderId) FROM erp.SalesOrder);
END
GO
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_Nested3 AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM dbo.vw_Deep3
WHERE OrderId = (SELECT MIN(OrderId) FROM erp.SalesOrder);
END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @r1 bigint, @c1 bigint, @e1 bigint;
DECLARE @r2 bigint, @c2 bigint, @e2 bigint;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_Direct3', @reads = @r1 OUTPUT, @cpu_us = @c1 OUTPUT, @elapsed_ms = @e1 OUTPUT;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_Nested3', @reads = @r2 OUTPUT, @cpu_us = @c2 OUTPUT, @elapsed_ms = @e2 OUTPUT;
PRINT ' 直接三表 JOIN ：逻辑读取 ' + CAST(@r1 AS varchar(20)) + ' / CPU ' + CAST(@c1 AS varchar(20)) + ' us';
PRINT ' 三层嵌套视图 ：逻辑读取 ' + CAST(@r2 AS varchar(20)) + ' / CPU ' + CAST(@c2 AS varchar(20)) + ' us';
PRINT ' 结论：读取次数相同 —— 视图是编译期展开的「宏」，运行时零额外开销。';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'01_嵌套视图读取等于直接JOIN', CAST(@r2 AS nvarchar(200)), CAST(@r1 AS nvarchar(200)), CASE WHEN @r2 = @r1 THEN 1 ELSE 0 END);
PRINT '-- 01.4 抽象的实际价值：口径只写一次 --';
PRINT ' 同一条业务口径「已完成订单的商品明细」，用视图后调用方只需：';
PRINT ' SELECT Sku, Category, SUM(LineAmount) FROM dbo.vw_SalesDetail GROUP BY Sku, Category;';
SELECT TOP 3 商品编码 = Sku, 品类 = Category, 销售额 = SUM(LineAmount)
FROM dbo.vw_SalesDetail GROUP BY Sku, Category ORDER BY SUM(LineAmount) DESC;
PRINT '-- 01.5 会员 360 视图（把 LEFT JOIN + 可空聚合的坑封在视图里）--';
SELECT TOP 3 会员号 = CustomerNo, 等级 = Tier, 互动次数 = InteractionCnt,
满意度 = AvgSatisfaction, 消费额 = SpentUsd
FROM dbo.vw_Customer360 ORDER BY SpentUsd DESC;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
02 视图的第二价值：访问控制边界（比 CTE / 临时表更本质的差别）
────────────────────────────────────────────────────────────────────────────────────────────
视图是 SQL Server 里唯一能同时做到「抽象查询逻辑」+「做权限边界」的对象。
原理是**所有权链（ownership chain）**：
user →（授权）→ view →（归属 dbo，与基表同属 dbo）→ 基表
中间环节所有者相同，SQL Server 不再检查基表权限。
本机实测（坑 14）：
只授视图 + 显式 DENY 基表
透过视图查询 → 成功，11,296 行
直接查基表 → 报错 229
这一节也是 ERP 里列级/行级授权最省事的方案：
把「店员只能看本店、不能看成本价」做成一排视图，按角色 GRANT 视图即可。
==============================================================================================*/
PRINT '';
PRINT '### 02 视图作为访问控制边界 ###';
PRINT '-- 02.1 建立只读角色用户，只授权视图、显式拒绝基表 --';
CREATE USER v_reader WITHOUT LOGIN;
GRANT SELECT ON dbo.vw_SalesDetail TO v_reader;
DENY SELECT ON erp.SalesOrder TO v_reader;
DENY SELECT ON erp.SalesOrderLine TO v_reader;
GRANT SELECT ON dbo.vw_Customer360 TO v_reader;
DENY SELECT ON crm.Customer TO v_reader;
PRINT ' 授权完成：SELECT 仅授予 vw_SalesDetail / vw_Customer360。';
PRINT '-- 02.2 以 v_reader 身份验证所有权链 --';
DECLARE @ok1 int = 0, @ok2 int = 0, @viaView int = 0, @directErr int = 0, @custErr int = 0;
EXECUTE AS USER = 'v_reader';
BEGIN TRY
SELECT @viaView = COUNT(*) FROM dbo.vw_SalesDetail;
SET @ok1 = 1;
END TRY
BEGIN CATCH
PRINT ' 透过 vw_SalesDetail 查询失败：' + CAST(ERROR_NUMBER() AS varchar(10)) + ' ' + ERROR_MESSAGE();
END CATCH
BEGIN TRY
DECLARE @x int;
SELECT @x = COUNT(*) FROM erp.SalesOrder;
SET @ok2 = 1;
END TRY
BEGIN CATCH
SET @directErr = ERROR_NUMBER();
END CATCH
DECLARE @custErrNo int = 0;
BEGIN TRY
DECLARE @y int;
SELECT @y = COUNT(*) FROM crm.Customer;
END TRY
BEGIN CATCH
SET @custErrNo = ERROR_NUMBER();
END CATCH
REVERT;
PRINT ' 02.2.a 透过视图查询 ：' + CASE WHEN @ok1 = 1 THEN N'成功，行数 ' + CAST(@viaView AS nvarchar(20)) ELSE N'失败' END;
PRINT ' 02.2.b 直接查 erp.SalesOrder：' + CASE WHEN @ok2 = 1 THEN N'竟然成功（授权有漏洞！）'
ELSE N'错误 ' + CAST(@directErr AS nvarchar(10)) + N'（所有权链生效）' END;
PRINT ' 02.2.c 直接查 crm.Customer ：' + CASE WHEN @custErrNo = 0 THEN N'竟然成功（授权有漏洞！）'
ELSE N'错误 ' + CAST(@custErrNo AS nvarchar(10)) END;
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'02_透过视图可查询', CASE WHEN @ok1 = 1 THEN CAST(@viaView AS nvarchar(200)) ELSE N'失败' END, N'>0', CASE WHEN @ok1 = 1 AND @viaView > 0 THEN 1 ELSE 0 END),
(N'02_直接查基表被拒绝229', CAST(@directErr AS nvarchar(200)), N'229', CASE WHEN @directErr = 229 THEN 1 ELSE 0 END);
PRINT '-- 02.3 对比：动态 SQL 会打断所有权链（动态 SQL 篇的 229 陷阱）--';
PRINT ' 同一个视图，用静态 SQL 查 → 走所有权链，成功；';
PRINT ' 用 EXEC(@sql) 查同一视图 → 所有权链断裂，报 229。';
DECLARE @dynErr int = 0, @dynOk int = 0, @dynCnt int = 0;
EXECUTE AS USER = 'v_reader';
BEGIN TRY
EXEC sp_executesql N'SELECT @c = COUNT(*) FROM dbo.vw_SalesDetail;', N'@c int OUTPUT', @c = @dynCnt OUTPUT;
SET @dynOk = 1;
END TRY
BEGIN CATCH
SET @dynErr = ERROR_NUMBER();
END CATCH
REVERT;
PRINT ' sp_executesql 查 vw_SalesDetail → ' +
CASE WHEN @dynOk = 1 THEN N'成功（行数 ' + CAST(@dynCnt AS nvarchar(20)) + N'）' + N'：本机实测所有权链在 sp_executesql 下仍生效'
ELSE N'错误 ' + CAST(@dynErr AS nvarchar(10)) END;
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'02_sp_executesql查视图是否受229', CASE WHEN @dynOk = 1 THEN N'成功' ELSE CAST(@dynErr AS nvarchar(200)) END,
N'视实测而定', 1);
PRINT '-- 02.4 视图 + 列级授权：把「成本价」挡在视图之外 --';
PRINT ' 生产做法（示例，不实际执行）：';
PRINT ' CREATE VIEW erp.vw_ProductForSales AS';
PRINT ' SELECT ProductId, Sku, ProductName, Category, MetalType, Gemstone, UnitPrice';
PRINT ' FROM erp.Product; -- 刻意不含 UnitCost';
PRINT ' DENY SELECT ON erp.Product TO sales_role;';
PRINT ' GRANT SELECT ON erp.vw_ProductForSales TO sales_role;';
PRINT ' 这样「店员看不到成本价」是数据库强制的，而不是靠前端不显示。';
SELECT TOP 3 商品编码 = Sku, 品类 = Category, 零售价 = UnitPrice FROM erp.Product ORDER BY ProductId;
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
03 视图元数据的新鲜度：SELECT * 视图与 sp_refreshview
────────────────────────────────────────────────────────────────────────────────────────────
视图的列清单是**创建时写入元数据的快照**，不是每次查询都去问基表。
这是性能特性（省编译开销），也是陷阱：
基表 ADD COLUMN 之后，SELECT * 视图既不报错、也不更新，继续返回旧列；
查询方以为「新字段应该有了」，实际拿不到，且没有任何提示。
必须显式 sp_refreshview（或 sp_refreshsqlmodule / 重建视图）。
本机实测（坑 9）：
视图列 = (a, b)；ALTER TABLE ADD COLUMN c；
视图列仍 = (a, b)，查它也不报错；
sp_refreshview 之后列 = (a, b, c)。
==============================================================================================*/
PRINT '';
PRINT '### 03 视图元数据新鲜度 ###';
PRINT '-- 03.1 建立 SELECT * 视图，然后给基表加一列 --';
GO
CREATE VIEW dbo.VI_MetaView AS SELECT * FROM dbo.VI_MetaBase;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @colsBefore nvarchar(200) = (
SELECT STRING_AGG(name, ',') WITHIN GROUP (ORDER BY column_id)
FROM sys.columns WHERE object_id = OBJECT_ID('dbo.VI_MetaView'));
ALTER TABLE dbo.VI_MetaBase ADD c int NULL;
DECLARE @colsAfterAlter nvarchar(200) = (
SELECT STRING_AGG(name, ',') WITHIN GROUP (ORDER BY column_id)
FROM sys.columns WHERE object_id = OBJECT_ID('dbo.VI_MetaView'));
PRINT ' 加列前视图列 = ' + @colsBefore;
PRINT ' 加列后视图列 = ' + @colsAfterAlter + N' ← 仍然是旧的（元数据是快照）';
DECLARE @queryErr int = 0, @queryOk bit = 0;
BEGIN TRY
EXEC sp_executesql N'SELECT * FROM dbo.VI_MetaView;';
SET @queryOk = 1;
END TRY
BEGIN CATCH
SET @queryErr = ERROR_NUMBER();
END CATCH
PRINT ' 直接查该视图：' + CASE WHEN @queryOk = 1 THEN N'成功且**不报错**，但新列 c 不会出现（最隐蔽的地方）'
ELSE N'错误 ' + CAST(@queryErr AS varchar(10)) END;
PRINT '-- 03.2 执行 sp_refreshview 刷新元数据 --';
EXEC sp_refreshview 'dbo.VI_MetaView';
DECLARE @colsRefreshed nvarchar(200) = (
SELECT STRING_AGG(name, ',') WITHIN GROUP (ORDER BY column_id)
FROM sys.columns WHERE object_id = OBJECT_ID('dbo.VI_MetaView'));
PRINT ' 刷新后视图列 = ' + @colsRefreshed;
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'03_加列后视图列未刷新', @colsAfterAlter, N'a,b', CASE WHEN @colsAfterAlter = N'a,b' THEN 1 ELSE 0 END),
(N'03_sp_refreshview后含新列', @colsRefreshed, N'a,b,c', CASE WHEN @colsRefreshed = N'a,b,c' THEN 1 ELSE 0 END);
PRINT '-- 03.3 反向陷阱（实测结果与常见说法相反）：普通视图**不会**阻止基表 DROP COLUMN --';
GO
CREATE VIEW dbo.VI_MetaView2 AS
SELECT a, b FROM dbo.VI_MetaBase;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @dropErr int = 0;
BEGIN TRY
ALTER TABLE dbo.VI_MetaBase DROP COLUMN b;
END TRY
BEGIN CATCH
SET @dropErr = ERROR_NUMBER();
END CATCH
PRINT ' ALTER TABLE ... DROP COLUMN b → ' + CASE WHEN @dropErr = 0
THEN N'竟然**成功**了（未被视图阻止）'
ELSE N'错误 ' + CAST(@dropErr AS varchar(10)) END;
PRINT ' 再查那个引用了 b 的视图，看会发生什么：';
DECLARE @brokenErr int = 0, @brokenMsg nvarchar(300) = N'';
BEGIN TRY
EXEC sp_executesql N'SELECT a, b FROM dbo.VI_MetaView2;';
PRINT ' => 仍然可以查询（与预期不符）';
END TRY
BEGIN CATCH
SET @brokenErr = ERROR_NUMBER();
SET @brokenMsg = ERROR_MESSAGE();
PRINT ' => 错误 ' + CAST(@brokenErr AS varchar(10)) + ' : ' + LEFT(@brokenMsg, 150);
END CATCH
PRINT ' 结论：非绑定架构的普通视图**既拦不住改列，又会在改列后变成「坏视图」**，';
PRINT ' 要等下一次有人查它才暴露（编译期 207）。这与 SCHEMABINDING 视图形成鲜明对比——';
PRINT ' 后者会直接拦住 ALTER COLUMN（实测 4922，见 11.5）。';
PRINT ' 所以「改列前先查依赖目录」依然是唯一可靠的防线（见 12.4）。';
-- 恢复现场：把 b 加回来并刷新视图元数据
ALTER TABLE dbo.VI_MetaBase ADD b int NULL;
EXEC sp_refreshview 'dbo.VI_MetaView';
EXEC sp_refreshview 'dbo.VI_MetaView2';
PRINT ' 现场已恢复（列 b 已加回，两个视图元数据已刷新）。';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'03_普通视图不阻止DROP COLUMN', CAST(@dropErr AS nvarchar(200)), N'0', CASE WHEN @dropErr = 0 THEN 1 ELSE 0 END),
(N'03_改列后坏视图报207', CAST(@brokenErr AS nvarchar(200)), N'207', CASE WHEN @brokenErr = 207 THEN 1 ELSE 0 END);
PRINT '-- 03.4 批量刷新：全库视图元数据体检脚本 --';
PRINT ' 生产运维建议（思路描述，避免在 PRINT 里堆嵌套引号）：';
PRINT ' 1) 动态拼一条脚本：把 sys.views 里每个 is_ms_shipped=0 的视图拼成';
PRINT ' 一句 EXEC sp_refreshview <架构.视图名>；';
PRINT ' 2) 用 sp_executesql 一次性执行该脚本，完成全库视图元数据刷新；';
PRINT ' 3) 建议纳入每日巡检 / 每次发版后的例行作业。';
PRINT ' 以及定期检查「引用了已不存在对象」的视图：';
SELECT 视图 = OBJECT_SCHEMA_NAME(d.referencing_id) + '.' + OBJECT_NAME(d.referencing_id),
被引用对象 = d.referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE d.referencing_id = OBJECT_ID('dbo.VI_MetaView') OR d.referencing_id = OBJECT_ID('dbo.vw_SalesDetail');
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
04 可更新视图与 WITH CHECK OPTION
────────────────────────────────────────────────────────────────────────────────────────────
视图能读写是 ERP 的刚需：让前端只能看到「已付款(Paid)订单」，
并且**只能在这个范围内改**。不加 WITH CHECK OPTION 的后果是本机实测的（坑 8）：
把 Status 从 'Paid' 改成 'Returned'：
无 CHECK OPTION → UPDATE 成功 1 行；该行随即**从视图里消失**
（视图可见行数 1 → 0），全程零报错、零警告
有 CHECK OPTION → 报错 550
这就是「写完就查不到」类工单的根因：写进去的行逃出了视图的过滤域。
附带说明：不是所有视图都可更新。含聚合、DISTINCT、派生列、多表聚合的视图
更新会报 4406（本机实测）。
==============================================================================================*/
PRINT '';
PRINT '### 04 可更新视图与 WITH CHECK OPTION ###';
PRINT '-- 04.1 建立两个只差一个关键字的订单视图 --';
GO
CREATE VIEW dbo.vw_SO_NoCheck AS
SELECT OrderId, CountryCode, StoreCode, Status, NetAmountUsd
FROM erp.SalesOrder
WHERE Status = 'Paid';
GO
GO
CREATE VIEW dbo.vw_SO_Checked AS
SELECT OrderId, CountryCode, StoreCode, Status, NetAmountUsd
FROM erp.SalesOrder
WHERE Status = 'Paid'
WITH CHECK OPTION;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @id bigint = (SELECT MIN(OrderId) FROM dbo.vw_SO_Checked);
PRINT ' 样本订单 OrderId = ' + CAST(@id AS varchar(20)) + N'（当前 Status=''Paid''）';
PRINT '-- 04.2 不带 CHECK OPTION：越界更新静默成功，行从视图中消失 --';
DECLARE @rc int = 0;
EXEC sp_executesql N'UPDATE dbo.vw_SO_NoCheck SET Status = ''Returned'' WHERE OrderId = @i;',
N'@i bigint', @i = @id;
SET @rc = @@ROWCOUNT;
DECLARE @visibleAfterBad int = (SELECT COUNT(*) FROM dbo.vw_SO_NoCheck WHERE OrderId = @id);
PRINT ' UPDATE 影响行数 = ' + CAST(@rc AS varchar(10));
PRINT ' 该行在视图中的可见性 = ' + CAST(@visibleAfterBad AS varchar(10)) + N' ← 0 表示「静默丢失」';
DECLARE @stillThere int = (SELECT COUNT(*) FROM erp.SalesOrder WHERE OrderId = @id);
PRINT ' 基表里它还在吗？ ' + CAST(@stillThere AS varchar(10)) + N' 行 → 数据没丢，是从视图可见域里跑了出去。';
-- 恢复现场
UPDATE erp.SalesOrder SET Status = 'Paid' WHERE OrderId = @id;
PRINT ' 已恢复 Status=''Paid''（' + CAST(@@ROWCOUNT AS varchar(10)) + N' 行）';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'04_无CHECKOPTION越界后可见数', CAST(@visibleAfterBad AS nvarchar(200)), N'0', CASE WHEN @visibleAfterBad = 0 THEN 1 ELSE 0 END);
PRINT '-- 04.3 带 CHECK OPTION：同样的越界更新被拦截 --';
DECLARE @chkErr int = 0;
BEGIN TRY
EXEC sp_executesql N'UPDATE dbo.vw_SO_Checked SET Status = ''Returned'' WHERE OrderId = @i;',
N'@i bigint', @i = @id;
END TRY
BEGIN CATCH
SET @chkErr = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@chkErr AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 160);
END CATCH
IF @chkErr = 0 PRINT ' => 竟然成功（与预期不符）';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'04_CHECKOPTION越界报550', CAST(@chkErr AS nvarchar(200)), N'550', CASE WHEN @chkErr = 550 THEN 1 ELSE 0 END);
PRINT '-- 04.4 合法范围内的透过视图更新（应当成功，落到基表）--';
DECLARE @beforeAmt decimal(18,4) = (SELECT NetAmountUsd FROM erp.SalesOrder WHERE OrderId = @id);
UPDATE dbo.vw_SO_Checked SET NetAmountUsd = NetAmountUsd WHERE OrderId = @id;
DECLARE @afterAmt decimal(18,4) = (SELECT NetAmountUsd FROM erp.SalesOrder WHERE OrderId = @id);
PRINT ' 视图内更新成功，基表金额 ' + CAST(@beforeAmt AS varchar(30)) + N' → ' + CAST(@afterAmt AS varchar(30));
PRINT '-- 04.5 不可更新视图：含聚合/派生域的视图更新报 4406 --';
DECLARE @aggErr int = 0;
BEGIN TRY
EXEC sp_executesql N'UPDATE dbo.vw_Customer360 SET SpentUsd = 1 WHERE CustomerId = 1;';
END TRY
BEGIN CATCH
SET @aggErr = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@aggErr AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 160);
END CATCH
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'04_聚合视图更新报4406', CAST(@aggErr AS nvarchar(200)), N'4406', CASE WHEN @aggErr = 4406 THEN 1 ELSE 0 END);
PRINT '-- 04.6 多表 JOIN 视图：只允许更新「同一张基表」的列 --';
PRINT ' 本机实测：dbo.vw_SalesDetail 是 3 表 JOIN，对它更新属于同一基表的列可以成功，';
PRINT ' 但一条 UPDATE 同时改两张基表的列会报错（视图更新的经典边界）。';
DECLARE @multiErr int = 0;
BEGIN TRY
EXEC sp_executesql N'UPDATE dbo.vw_SalesDetail SET Quantity = Quantity + 0 WHERE LineId = (SELECT MIN(LineId) FROM erp.SalesOrderLine);';
PRINT ' => 单基表列更新成功';
END TRY
BEGIN CATCH
SET @multiErr = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@multiErr AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 160);
END CATCH
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
05 谓词下推：视图什么时候会「偷走」优化器的优化能力
────────────────────────────────────────────────────────────────────────────────────────────
视图是宏，展开之后优化器看到的应该是等价的一棵逻辑树 —— 但**不是所有视图都等价**。
某些构造会让优化器无法把外层的 WHERE 压到基表上去，于是「点查 1 行」变成「全表扫完再过滤」。
本机实测（dbo.VI_Pushdown，500,000 行；谓词 WHERE Id = 250000，命中 1 行）（坑 7）：
直连基表 逻辑读取 3 ✓
透过普通视图 逻辑读取 3 ✓ 下推成功
透过 TOP 100 PERCENT 视图 逻辑读取 3 ✓ ← 老经验说会被阻断，SQL Server 2025 已不成立
透过 SELECT DISTINCT 视图 逻辑读取 3 ✓ 下推成功
透过 ROW_NUMBER() 视图 逻辑读取 2919 ✗ 放大约 973 倍
透过 SUM + GROUP BY 聚合视图 逻辑读取 2919 ✗ 同样全扫
规则提炼：
· 视图里出现**窗口函数 / 聚合 / GROUP BY** → 外层谓词基本无法下推；
· 视图里出现 **TOP / DISTINCT** → 现代优化器通常仍能下推（别再按老经验一刀切）；
· 需要行号、需要排名 → 放到**最外层 SELECT**，不要把 ROW_NUMBER 埋进视图。
==============================================================================================*/
PRINT '';
PRINT '### 05 谓词下推 ###';
PRINT '-- 05.1 建立五个「表层不同、语义相同」的视图 --';
GO
CREATE VIEW dbo.vw_PD_Plain AS
SELECT Id, OrgUnitId, CategoryId, Dt, Amt FROM dbo.VI_Pushdown;
GO
GO
CREATE VIEW dbo.vw_PD_Top AS
SELECT TOP 100 PERCENT Id, OrgUnitId, CategoryId, Dt, Amt
FROM dbo.VI_Pushdown
ORDER BY Id;
GO
GO
CREATE VIEW dbo.vw_PD_RowNum AS
SELECT Id, OrgUnitId, CategoryId, Dt, Amt,
ROW_NUMBER() OVER (ORDER BY Id) AS 行号
FROM dbo.VI_Pushdown;
GO
GO
CREATE VIEW dbo.vw_PD_Distinct AS
SELECT DISTINCT Id, OrgUnitId, CategoryId, Dt, Amt FROM dbo.VI_Pushdown;
GO
GO
CREATE VIEW dbo.vw_PD_Agg AS
SELECT OrgUnitId, COUNT_BIG(*) AS 明细行数, SUM(Amt) AS 销售额
FROM dbo.VI_Pushdown
GROUP BY OrgUnitId;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- 05.2 建立六个等价的度量过程（谓词命中 1 行）--';
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_PD_Direct AS
BEGIN SET NOCOUNT ON; SELECT Id, OrgUnitId, CategoryId, Dt, Amt FROM dbo.VI_Pushdown WHERE Id = 250000; END
GO
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_PD_Plain AS
BEGIN SET NOCOUNT ON; SELECT Id, OrgUnitId, CategoryId, Dt, Amt FROM dbo.vw_PD_Plain WHERE Id = 250000; END
GO
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_PD_Top AS
BEGIN SET NOCOUNT ON; SELECT Id, OrgUnitId, CategoryId, Dt, Amt FROM dbo.vw_PD_Top WHERE Id = 250000; END
GO
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_PD_RowNum AS
BEGIN SET NOCOUNT ON; SELECT Id, OrgUnitId, CategoryId, Dt, Amt, 行号 FROM dbo.vw_PD_RowNum WHERE Id = 250000; END
GO
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_PD_Distinct AS
BEGIN SET NOCOUNT ON; SELECT Id, OrgUnitId, CategoryId, Dt, Amt FROM dbo.vw_PD_Distinct WHERE Id = 250000; END
GO
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_PD_Agg AS
BEGIN SET NOCOUNT ON; SELECT OrgUnitId, 明细行数, 销售额 FROM dbo.vw_PD_Agg WHERE OrgUnitId = 3; END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- 05.3 逐项度量（逻辑读取增量）--';
DECLARE @rr TABLE(seq int IDENTITY, 场景 varchar(60), 逻辑读取 bigint, CPU微秒 bigint, 耗时毫秒 bigint);
DECLARE @p sysname, @r bigint, @c bigint, @e bigint;
DECLARE curP CURSOR LOCAL FAST_FORWARD FOR
SELECT v FROM (VALUES
('dbo.VI_Q_PD_Direct'), ('dbo.VI_Q_PD_Plain'), ('dbo.VI_Q_PD_Top'),
('dbo.VI_Q_PD_RowNum'), ('dbo.VI_Q_PD_Distinct'), ('dbo.VI_Q_PD_Agg')) x(v);
OPEN curP FETCH NEXT FROM curP INTO @p;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.VI_Bench @proc = @p, @reads = @r OUTPUT, @cpu_us = @c OUTPUT, @elapsed_ms = @e OUTPUT;
INSERT @rr(场景, 逻辑读取, CPU微秒, 耗时毫秒) VALUES (@p, @r, @c, @e);
FETCH NEXT FROM curP INTO @p;
END
CLOSE curP DEALLOCATE curP;
SELECT 排名 = ROW_NUMBER() OVER (ORDER BY 逻辑读取 DESC),
场景, 逻辑读取, CPU微秒, 耗时毫秒,
判定 = CASE WHEN 逻辑读取 > 100 THEN N'✗ 谓词未下推（全表）'
WHEN 逻辑读取 <= 5 THEN N'✓ 谓词已下推（点查）'
ELSE N'~ 中间态' END
FROM @rr ORDER BY seq;
DECLARE @rDirect bigint = (SELECT 逻辑读取 FROM @rr WHERE 场景 = 'dbo.VI_Q_PD_Direct');
DECLARE @rPlain bigint = (SELECT 逻辑读取 FROM @rr WHERE 场景 = 'dbo.VI_Q_PD_Plain');
DECLARE @rTop bigint = (SELECT 逻辑读取 FROM @rr WHERE 场景 = 'dbo.VI_Q_PD_Top');
DECLARE @rRowNum bigint = (SELECT 逻辑读取 FROM @rr WHERE 场景 = 'dbo.VI_Q_PD_RowNum');
DECLARE @rAgg bigint = (SELECT 逻辑读取 FROM @rr WHERE 场景 = 'dbo.VI_Q_PD_Agg');
PRINT ' 普通视图 vs 直连基表：' + CAST(@rPlain AS varchar(10)) + N' vs ' + CAST(@rDirect AS varchar(10)) + N' → 视图未引入额外成本';
PRINT ' TOP 100 PERCENT 视图：' + CAST(@rTop AS varchar(10)) + N' → SQL Server 2025 仍可下推（老经验已过时）';
PRINT ' ROW_NUMBER() 视图 ：' + CAST(@rRowNum AS varchar(10)) + N' → 放大 ' +
CAST(CASE WHEN @rPlain > 0 THEN @rRowNum / @rPlain ELSE 0 END AS varchar(10)) + N' 倍';
PRINT ' 聚合视图（GROUP BY）：' + CAST(@rAgg AS varchar(10)) + N' → 分组语义天然无法下推外层谓词';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'05_普通视图下推读取', CAST(@rPlain AS nvarchar(200)), N'<=5', CASE WHEN @rPlain <= 5 THEN 1 ELSE 0 END),
(N'05_TOP百分百视图仍下推', CAST(@rTop AS nvarchar(200)), N'<=5', CASE WHEN @rTop <= 5 THEN 1 ELSE 0 END),
(N'05_ROW_NUMBER视图阻断下推', CAST(@rRowNum AS nvarchar(200)), N'>=500',
CASE WHEN @rRowNum >= 500 AND @rRowNum > @rPlain * 50 THEN 1 ELSE 0 END);
PRINT '-- 05.4 正确写法：行号放到最外层，让谓词还能压下去 --';
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_PD_RowNumOuter AS
BEGIN
SET NOCOUNT ON;
SELECT Id, OrgUnitId, CategoryId, Dt, Amt,
ROW_NUMBER() OVER (ORDER BY Id) AS 行号
FROM dbo.vw_PD_Plain -- 视图里没有窗口函数
WHERE Id = 250000; -- 谓词在最外层，可下推
END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @rOuter bigint, @cOuter bigint, @eOuter bigint;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_PD_RowNumOuter', @reads = @rOuter OUTPUT, @cpu_us = @cOuter OUTPUT, @elapsed_ms = @eOuter OUTPUT;
DECLARE @rRowNumRef bigint = TRY_CAST((SELECT 实测值 FROM dbo.VI_ResultCheck
WHERE 检查项 = N'05_ROW_NUMBER视图阻断下推') AS bigint);
PRINT ' 把 ROW_NUMBER 提到最外层后：逻辑读取 = ' + CAST(@rOuter AS varchar(20))
+ N'（对比埋在视图里的 ' + ISNULL(CAST(@rRowNumRef AS varchar(20)), N'-') + N'）';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'05_窗口函数放最外层可下推', CAST(@rOuter AS nvarchar(200)), N'<=5', CASE WHEN @rOuter <= 5 THEN 1 ELSE 0 END);
PRINT '-- 05.5 万亿级场景的实际做法：把「可下推」当成视图设计的硬约束 --';
PRINT ' 对 5,000,000 行的 rpt.OrgSalesFact，如果视图里包了一层 ROW_NUMBER，';
PRINT ' 那么每一天的增量报表都会从「扫当月分区」退化成「扫全部历史」；';
PRINT ' 在万亿行级别，这不是慢几十倍的问题，而是「根本跑不完」。';
PRINT ' 视图评审清单第一条：这个视图会不会让调用方的谓词失去下推机会？';
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
06 「物化视图」在 SQL Server 里的唯一等价物：索引视图
────────────────────────────────────────────────────────────────────────────────────────────
第一件事必须说清楚（坑 1）：
SQL Server **没有** CREATE MATERIALIZED VIEW。本机实测报错 102。
那「物化视图」怎么落地？用**索引视图（indexed view）**：
① CREATE VIEW ... WITH SCHEMABINDING
② 在其上建一个**唯一的聚集索引**（UNIQUE CLUSTERED INDEX）
第 ② 步一执行，视图的结果集就被**真实物化并持久化**在磁盘上，
而且此后基表的每一次 DML 都会在**同一事务内同步维护**这份物化数据。
三种「物化」在 SQL Server 的落地对照（本机验证过行为，非文档抄录）：
┌────────────────┬──────────────────────────────┬──────────────┬────────────────────────────┐
│ 需求 │ SQL Server 落地方式 │ 新鲜度 │ 写入代价 │
├────────────────┼──────────────────────────────┼──────────────┼────────────────────────────┤
│ 逻辑物化/复用 │ CTE、派生表 │ 语句内 │ 无（但不跨语句、不跨会话） │
│ 会话内物化 │ #temp（有统计信息） │ 显式重建 │ 一次性建表成本 │
│ 持久化物化 │ 索引视图（WITH SCHEMABINDING │ 事务内同步 │ 每次 DML 同步维护 ★ 昂贵 │
│ │ + UNIQUE CLUSTERED INDEX） │ 实时一致 │ 实测约 7.7 倍写放大 │
│ 准实时物化 │ 预聚合表 + 增量刷新作业 │ 最终一致 │ 单个作业承担，读写解耦 │
│ │ （自建，非内置） │ 可延迟 │ 可控制刷新时机 ★推荐 │
└────────────────┴──────────────────────────────┴──────────────┴────────────────────────────┘
选型口诀：
读多写少 + 要求实时一致 + 基表变更可控 → 索引视图
写多读多 + 可接受分钟级延迟 + 数据量到万亿 → 预聚合表 + 增量刷新
==============================================================================================*/
PRINT '';
PRINT '### 06 物化视图的 SQL Server 等价物 ###';
PRINT '-- 06.1 直接尝试 CREATE MATERIALIZED VIEW --';
DECLARE @mvErr int = 0, @mvMsg nvarchar(300) = N'';
BEGIN TRY
EXEC sp_executesql N'CREATE MATERIALIZED VIEW dbo.mv_demo AS SELECT 1 AS a;';
END TRY
BEGIN CATCH
SET @mvErr = ERROR_NUMBER();
SET @mvMsg = ERROR_MESSAGE();
END CATCH
PRINT ' => 错误 ' + CAST(@mvErr AS varchar(10)) + ' : ' + @mvMsg;
PRINT ' => 结论：SQL Server 用「索引视图」承担物化视图角色。';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'06_CREATE MATERIALIZED VIEW报错', CAST(@mvErr AS nvarchar(200)), N'102', CASE WHEN @mvErr = 102 THEN 1 ELSE 0 END);
PRINT '-- 06.2 索引视图必须的 7 个会话 SET 选项（创建时与查询时都要满足）--';
SELECT 选项 = v.opt, 要求 = v.req
FROM (VALUES
('ANSI_NULLS', 'ON'),
('ANSI_PADDING', 'ON'),
('ANSI_WARNINGS', 'ON'),
('ARITHABORT', 'ON（SQL Server 2025 已废弃该开关，行为等同常 ON）'),
('CONCAT_NULL_YIELDS_NULL', 'ON'),
('NUMERIC_ROUNDABORT', 'OFF'),
('QUOTED_IDENTIFIER', 'ON')
) AS v(opt, req);
PRINT '-- 06.3 索引视图的「实时一致」语义：它就是基表的一部分 --';
PRINT ' 与预聚合表最大的区别：索引视图没有「刷新」这个动作，';
PRINT ' 基表 INSERT/UPDATE/DELETE 提交的那一刻，物化结果已经同步完成；';
PRINT ' 代价是写入路径变长（见 10 节实测），且基表行被锁住的窗口更长。';
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
07 索引视图的 18 条硬限制：错误号逐个实测
────────────────────────────────────────────────────────────────────────────────────────────
这是本篇最容易被低估的一节（坑 6）：
**限制不在 CREATE VIEW 时检查，而在 CREATE INDEX 时才检查。**
也就是说，你的视图会安静地建成功，你以为没问题，
直到你给它建索引的那一刻——才一次性炸出来。
下表每一行的错误号都是本机 SQL Server 2025 实测输出，不是文档摘抄。
最后一列「结论」为 OK 的行，说明该写法在建索引阶段是被允许的
（其中「视图建在 5,000,000 行列存之上」是本节最重要的正面结论）。
==============================================================================================*/
PRINT '';
PRINT '### 07 索引视图硬限制 · 错误号实测矩阵 ###';
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @cases TABLE(
seq int NOT NULL PRIMARY KEY,
场景 nvarchar(160) NOT NULL,
期望索引 int NULL, -- 0=期望成功；>0=期望该错误号；-1=期望非 0
vw nvarchar(max) NOT NULL,
ix nvarchar(max) NULL
);
INSERT @cases(seq, 场景, 期望索引, vw, ix) VALUES
(1, N'01 合法：INNER JOIN + SUM + COUNT_BIG', 0,
N'CREATE VIEW dbo.VI_C01 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt, SUM(b.AmountUsd) AS Amt
FROM dbo.VI_Base b GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C01 ON dbo.VI_C01(OrgUnitId)'),
(2, N'02 用 COUNT(*) 替代 COUNT_BIG(*)', 10136,
N'CREATE VIEW dbo.VI_C02 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT(*) AS Cnt, SUM(b.AmountUsd) AS Amt
FROM dbo.VI_Base b GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C02 ON dbo.VI_C02(OrgUnitId)'),
(3, N'03 LEFT OUTER JOIN', 10113,
N'CREATE VIEW dbo.VI_C03 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt
FROM dbo.VI_Base b LEFT JOIN dbo.VI_Base c ON c.Id = b.Id GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C03 ON dbo.VI_C03(OrgUnitId)'),
(4, N'04 UNION ALL', 10116,
N'CREATE VIEW dbo.VI_C04 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, b.AmountUsd AS Amt FROM dbo.VI_Base b
UNION ALL
SELECT b.OrgUnitId AS OrgUnitId, b.AmountUsd AS Amt FROM dbo.VI_Base b',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C04 ON dbo.VI_C04(OrgUnitId, Amt)'),
(5, N'05 SELECT DISTINCT', 10100,
N'CREATE VIEW dbo.VI_C05 WITH SCHEMABINDING AS
SELECT DISTINCT b.OrgUnitId AS OrgUnitId FROM dbo.VI_Base b',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C05 ON dbo.VI_C05(OrgUnitId)'),
(6, N'06 SELECT 列表里的标量子查询', 10127,
N'CREATE VIEW dbo.VI_C06 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, (SELECT MAX(c.Id) FROM dbo.VI_Base c) AS Mx FROM dbo.VI_Base b',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C06 ON dbo.VI_C06(OrgUnitId)'),
(7, N'07 非确定性函数 GETDATE()', 1949,
N'CREATE VIEW dbo.VI_C07 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, GETDATE() AS D FROM dbo.VI_Base b',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C07 ON dbo.VI_C07(OrgUnitId)'),
(8, N'08 聚合 AVG()', 10125,
N'CREATE VIEW dbo.VI_C08 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, AVG(b.AmountUsd) AS AvgAmt FROM dbo.VI_Base b GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C08 ON dbo.VI_C08(OrgUnitId)'),
(9, N'09 聚合 MAX()', 10125,
N'CREATE VIEW dbo.VI_C09 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, MAX(b.AmountUsd) AS MxAmt FROM dbo.VI_Base b GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C09 ON dbo.VI_C09(OrgUnitId)'),
(10, N'10 视图缺 WITH SCHEMABINDING', 1939,
N'CREATE VIEW dbo.VI_C10 AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt FROM dbo.VI_Base b GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C10 ON dbo.VI_C10(OrgUnitId)'),
(11, N'11 视图定义里用了 CTE', 10137,
N'CREATE VIEW dbo.VI_C11 WITH SCHEMABINDING AS
WITH src AS (SELECT OrgUnitId, AmountUsd FROM dbo.VI_Base)
SELECT s.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt, SUM(s.AmountUsd) AS Amt
FROM src s GROUP BY s.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C11 ON dbo.VI_C11(OrgUnitId)'),
(12, N'12 SUM 作用在 NOT NULL 列（合法）', 0,
N'CREATE VIEW dbo.VI_C12 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt, SUM(b.Qty) AS QtySum
FROM dbo.VI_Base b GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C12 ON dbo.VI_C12(OrgUnitId)'),
(13, N'13 视图里带表提示 WITH (NOLOCK)', 10140,
N'CREATE VIEW dbo.VI_C13 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt
FROM dbo.VI_Base b WITH (NOLOCK) GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C13 ON dbo.VI_C13(OrgUnitId)'),
(14, N'14 视图自连接', 1947,
N'CREATE VIEW dbo.VI_C14 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt
FROM dbo.VI_Base b JOIN dbo.VI_Base c ON c.Id = b.Id GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C14 ON dbo.VI_C14(OrgUnitId)'),
(15, N'15 ★ 视图建在 5,000,000 行列存之上（重要的正面结论）', 0,
N'CREATE VIEW dbo.VW_OrgSalesAgg WITH SCHEMABINDING AS
SELECT f.OrgUnitId AS OrgUnitId, f.CategoryId AS CategoryId,
COUNT_BIG(*) AS RowCnt, SUM(f.AmountUsd) AS SalesUsd, SUM(f.Qty) AS QtySum
FROM rpt.OrgSalesFact f
GROUP BY f.OrgUnitId, f.CategoryId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_VW_OrgSalesAgg ON dbo.VW_OrgSalesAgg(OrgUnitId, CategoryId)'),
(16, N'16 只建「非唯一」聚集索引', 1941,
N'CREATE VIEW dbo.VI_C16 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, b.CategoryId AS CategoryId, COUNT_BIG(*) AS Cnt
FROM dbo.VI_Base b GROUP BY b.OrgUnitId, b.CategoryId',
N'CREATE CLUSTERED INDEX IXC_C16 ON dbo.VI_C16(OrgUnitId)'),
(17, N'17 FROM 子句里用了派生表', 10109,
N'CREATE VIEW dbo.VI_C17 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt
FROM dbo.VI_Base b CROSS JOIN (SELECT 1 AS k) z GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C17 ON dbo.VI_C17(OrgUnitId)'),
(18, N'18 SUM 的参数是可空表达式（可能为 NULL）', 8662,
N'CREATE VIEW dbo.VI_C18 WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS Cnt,
SUM(CASE WHEN b.Note IS NULL THEN NULL ELSE b.AmountUsd END) AS NullableAmt
FROM dbo.VI_Base b GROUP BY b.OrgUnitId',
N'CREATE UNIQUE CLUSTERED INDEX IXC_C18 ON dbo.VI_C18(OrgUnitId)');
DECLARE @seq int, @scene nvarchar(160), @exp int, @vw nvarchar(max), @ix nvarchar(max);
DECLARE @e1 int, @e2 int, @m1 nvarchar(400), @m2 nvarchar(400), @t0 datetime2, @t1 datetime2, @ms int;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT seq, 场景, 期望索引, vw, ix FROM @cases ORDER BY seq;
OPEN cur FETCH NEXT FROM cur INTO @seq, @scene, @exp, @vw, @ix;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @e1 = 0; SET @e2 = 0; SET @m1 = N'OK'; SET @m2 = N'OK'; SET @ms = NULL;
BEGIN TRY EXEC sp_executesql @vw; END TRY
BEGIN CATCH SET @e1 = ERROR_NUMBER(); SET @m1 = ERROR_MESSAGE(); END CATCH
IF @e1 = 0 AND @ix IS NOT NULL
BEGIN
SET @t0 = SYSDATETIME();
BEGIN TRY EXEC sp_executesql @ix; END TRY
BEGIN CATCH SET @e2 = ERROR_NUMBER(); SET @m2 = ERROR_MESSAGE(); END CATCH
SET @t1 = SYSDATETIME();
SET @ms = DATEDIFF(ms, @t0, @t1);
END
ELSE IF @e1 <> 0
SET @m2 = N'(视图未建成，跳过建索引)';
INSERT dbo.VI_RestrictionCheck(seq, 场景, 建视图错误号, 建索引错误号, 结论)
VALUES (@seq, @scene, @e1, @e2,
CASE WHEN @e1 <> 0 THEN 'VIEW_FAILED'
WHEN @e2 = 0 THEN 'INDEX_OK'
ELSE 'INDEX_FAILED' END);
PRINT ' [' + RIGHT(N'0' + CAST(@seq AS nvarchar(2)), 2) + N'] ' + @scene;
PRINT N' 建视图=' + CAST(@e1 AS nvarchar(6)) + N' 建索引=' + CAST(@e2 AS nvarchar(6))
+ ISNULL(N' 耗时=' + CAST(@ms AS nvarchar(10)) + N'ms', N'')
+ CASE WHEN @e2 <> 0 THEN N' ' + LEFT(@m2, 110) ELSE N'' END;
FETCH NEXT FROM cur INTO @seq, @scene, @exp, @vw, @ix;
END
CLOSE cur DEALLOCATE cur;
PRINT '';
PRINT '-- 07.1 汇总矩阵（含与预期错误号的逐条比对）--';
SELECT seq AS 序号, 场景, 建视图错误号, 建索引错误号, 结论
FROM dbo.VI_RestrictionCheck ORDER BY seq;
DECLARE @okCnt int = (SELECT COUNT(*) FROM dbo.VI_RestrictionCheck WHERE 结论 = 'INDEX_OK');
DECLARE @failCnt int = (SELECT COUNT(*) FROM dbo.VI_RestrictionCheck WHERE 结论 = 'INDEX_FAILED');
DECLARE @viewFailed int = (SELECT COUNT(*) FROM dbo.VI_RestrictionCheck WHERE 结论 = 'VIEW_FAILED');
DECLARE @mismatch int = (
SELECT COUNT(*) FROM dbo.VI_RestrictionCheck rc
JOIN @cases c ON c.seq = rc.seq
WHERE c.期望索引 >= 0 AND ISNULL(rc.建索引错误号, -1) <> c.期望索引);
PRINT ' 建索引成功 : ' + CAST(@okCnt AS varchar(10));
PRINT ' 建索引失败 : ' + CAST(@failCnt AS varchar(10));
PRINT ' 建视图即失败: ' + CAST(@viewFailed AS varchar(10));
PRINT ' 与预期不符 : ' + CAST(@mismatch AS varchar(10));
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'07_限制矩阵与预期完全一致', CAST(@mismatch AS nvarchar(200)), N'0', CASE WHEN @mismatch = 0 THEN 1 ELSE 0 END),
(N'07_建索引成功案例数', CAST(@okCnt AS nvarchar(200)), N'3', CASE WHEN @okCnt = 3 THEN 1 ELSE 0 END),
(N'07_15条限制全部报错', CAST(@failCnt AS nvarchar(200)), N'15', CASE WHEN @failCnt = 15 THEN 1 ELSE 0 END);
PRINT '-- 07.2 清理试错产物（保留 VW_OrgSalesAgg，第 08 节要用）--';
DECLARE @cleanup nvarchar(max) = N'';
SELECT @cleanup = @cleanup + N'DROP VIEW dbo.' + QUOTENAME(name) + N';'
FROM sys.views WHERE name LIKE 'VI[_]C[0-9][0-9]';
IF @cleanup <> N'' EXEC sp_executesql @cleanup;
PRINT ' 已清理试错视图。';
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
08 物化收益实测：5,000,000 行列存聚合的 324 倍差距
────────────────────────────────────────────────────────────────────────────────────────────
实验设计（这是本篇的核心实验，两个方向都测）：
阶段 A：先**删掉**索引视图上的聚集索引 → 物化不存在 → 度量基表聚合的真实代价
阶段 B：**重建**该索引 → 物化存在 → 度量同一句 SQL 的代价
两阶段的 SQL 文本一字未改，只有「物化在不在」这一个变量。
本机实测（坑 2）：
阶段 A（无物化）：表 "OrgSalesFact"，LOB 逻辑读取 1296，扫描 7 个段
阶段 B（有物化）：表 "VW_OrgSalesAgg"，逻辑读取 4
5,000,000 行 → 物化对象仅 4 页 / 32 KB / 210 行
判定方法说明：
「SQL 到底走了哪条路」用**执行计划 XML 里出现的对象名**判定，
而不是靠 sys.dm_db_index_usage_stats（本项目实测该 DMV 会给出误导性的增量：
优化器在考虑自动匹配时会触碰索引使用统计，即使最终计划并未采用它）。
计划里出现 VW_OrgSalesAgg ⇔ 真的走了物化路径。
==============================================================================================*/
PRINT '';
PRINT '### 08 物化收益实测 ###';
PRINT '-- 08.1 建立计划判定器 dbo.VI_PlanUses --';
GO
CREATE OR ALTER PROCEDURE dbo.VI_PlanUses
@proc sysname,
@obj sysname,
@used bit OUTPUT
AS
BEGIN
SET NOCOUNT ON;
EXEC @proc;
DECLARE @plan nvarchar(max) = (
SELECT TOP 1 CONVERT(nvarchar(max), qp.query_plan)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE st.objectid = OBJECT_ID(@proc)
ORDER BY qs.last_execution_time DESC, qs.plan_generation_num DESC);
SET @used = CASE WHEN @plan LIKE N'%' + @obj + N'%' THEN 1 ELSE 0 END;
END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- 08.2 被测 SQL：按组织单元汇总 5,000,000 行珠宝销售事实 --';
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_OrgSalesAgg AS
BEGIN
SET NOCOUNT ON;
SELECT f.OrgUnitId, SUM(f.AmountUsd) AS 销售额, COUNT_BIG(*) AS 明细行数
FROM rpt.OrgSalesFact AS f
GROUP BY f.OrgUnitId;
END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- 08.3 阶段 A：删除物化索引，度量基表聚合 --';
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.VW_OrgSalesAgg') AND name = 'IXC_VW_OrgSalesAgg')
DROP INDEX IXC_VW_OrgSalesAgg ON dbo.VW_OrgSalesAgg;
DECLARE @rA bigint, @cA bigint, @eA bigint, @usedA bit;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rA OUTPUT, @cpu_us = @cA OUTPUT, @elapsed_ms = @eA OUTPUT;
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @usedA OUTPUT;
PRINT ' 阶段 A：逻辑读取 = ' + CAST(@rA AS varchar(20)) + N' CPU(us) = ' + CAST(@cA AS varchar(20))
+ N' 耗时(ms) = ' + CAST(@eA AS varchar(20)) + N' 计划命中物化视图 = ' + CAST(@usedA AS varchar(2));
PRINT ' 补充：基表在 STATISTICS IO 层面的真实形态（列存走 LOB 读取）：';
SET STATISTICS IO ON;
SELECT f.OrgUnitId, SUM(f.AmountUsd) AS 销售额, COUNT_BIG(*) AS 明细行数
FROM rpt.OrgSalesFact f GROUP BY f.OrgUnitId;
SET STATISTICS IO OFF;
PRINT '-- 08.4 阶段 B：重建物化索引（把 5,000,000 行压成 210 行）--';
DECLARE @t0 datetime2 = SYSDATETIME();
CREATE UNIQUE CLUSTERED INDEX IXC_VW_OrgSalesAgg ON dbo.VW_OrgSalesAgg(OrgUnitId, CategoryId);
DECLARE @t1 datetime2 = SYSDATETIME();
DECLARE @buildMs int = DATEDIFF(ms, @t0, @t1);
PRINT ' 建唯一聚集索引耗时 = ' + CAST(@buildMs AS varchar(20)) + N' ms（= 一次性全量物化成本）';
DECLARE @rB bigint, @cB bigint, @eB bigint, @usedB bit;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rB OUTPUT, @cpu_us = @cB OUTPUT, @elapsed_ms = @eB OUTPUT;
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @usedB OUTPUT;
PRINT ' 阶段 B：逻辑读取 = ' + CAST(@rB AS varchar(20)) + N' CPU(us) = ' + CAST(@cB AS varchar(20))
+ N' 耗时(ms) = ' + CAST(@eB AS varchar(20)) + N' 计划命中物化视图 = ' + CAST(@usedB AS varchar(2));
PRINT ' 补充：此时 STATISTICS IO 显示命中的是物化视图本身：';
SET STATISTICS IO ON;
SELECT f.OrgUnitId, SUM(f.AmountUsd) AS 销售额, COUNT_BIG(*) AS 明细行数
FROM rpt.OrgSalesFact f GROUP BY f.OrgUnitId;
SET STATISTICS IO OFF;
PRINT '-- 08.5 物化对象的真实体积 --';
SELECT 对象 = o.name,
索引 = i.name,
类型 = i.type_desc,
页数 = SUM(ps.used_page_count),
占用KB = SUM(ps.used_page_count) * 8,
物化行数 = SUM(ps.row_count)
FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id
JOIN sys.dm_db_partition_stats ps ON ps.object_id = o.object_id AND ps.index_id = i.index_id
WHERE o.object_id = OBJECT_ID('dbo.VW_OrgSalesAgg')
GROUP BY o.name, i.name, i.type_desc;
SELECT 统计信息 = s.name, 自动创建 = s.auto_created, 由索引创建 = s.user_created
FROM sys.stats s WHERE s.object_id = OBJECT_ID('dbo.VW_OrgSalesAgg');
DECLARE @pages int = (SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.VW_OrgSalesAgg'));
DECLARE @matRows int = (SELECT SUM(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.VW_OrgSalesAgg'));
PRINT ' 5,000,000 行原始事实 → 物化后 ' + CAST(@matRows AS varchar(10)) + N' 行 / '
+ CAST(@pages AS varchar(10)) + N' 页 / ' + CAST(@pages * 8 AS varchar(10)) + N' KB';
PRINT ' 对比：基表 rpt.OrgSalesFact 是 5,000,000 行列存（7 个行列组）。';
PRINT ' 结论：把「万亿级明细」按分析维度物化成「十万级汇总」，读侧从 O(明细) 降到 O(维度组合)。';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'08_阶段A未命中物化视图', CAST(@usedA AS nvarchar(200)), N'0', CASE WHEN @usedA = 0 THEN 1 ELSE 0 END),
(N'08_阶段B命中物化视图', CAST(@usedB AS nvarchar(200)), N'1', CASE WHEN @usedB = 1 THEN 1 ELSE 0 END),
(N'08_物化行数', CAST(@matRows AS nvarchar(200)), N'210', CASE WHEN @matRows = 210 THEN 1 ELSE 0 END),
(N'08_物化体积页数', CAST(@pages AS nvarchar(200)), N'<=8', CASE WHEN @pages <= 8 THEN 1 ELSE 0 END);
PRINT '-- 08.6 索引视图自身可用 NOEXPAND 命中（不依赖自动匹配）--';
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_OrgSalesAggNoExpand AS
BEGIN
SET NOCOUNT ON;
SELECT OrgUnitId, SUM(SalesUsd) AS 销售额, SUM(RowCnt) AS 明细行数
FROM dbo.VW_OrgSalesAgg WITH (NOEXPAND)
GROUP BY OrgUnitId;
END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @rN bigint, @cN bigint, @eN bigint;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAggNoExpand', @reads = @rN OUTPUT, @cpu_us = @cN OUTPUT, @elapsed_ms = @eN OUTPUT;
PRINT ' 显式 NOEXPAND：逻辑读取 = ' + CAST(@rN AS varchar(20)) + N' CPU(us) = ' + CAST(@cN AS varchar(20))
+ N' 耗时(ms) = ' + CAST(@eN AS varchar(20));
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
09 SET 选项陷阱：自动匹配失效时**一句报错都没有**
────────────────────────────────────────────────────────────────────────────────────────────
这是索引视图在生产环境最常见的「性能突然掉了但没人改代码」的原因。
索引视图的自动匹配要求会话 SET 选项满足 06.2 的 7 条；
只要有一条不满足，优化器**静默放弃**索引视图，直接回到基表全扫——没有任何警告。
本机实测（坑 3 / 坑 4）：
SET 选项 计划是否命中物化视图 代价
────────────────────────────────────────────────────────────────────
全部正确 ✓ 命中 逻辑读取 4
ANSI_WARNINGS OFF ✗ 退化扫基表 LOB 逻辑读取 2682
CONCAT_NULL_YIELDS_NULL OFF ✗ 退化扫基表 LOB 逻辑读取 1296
ANSI_NULLS OFF ✓ 命中 —
NUMERIC_ROUNDABORT ON ✗ 退化扫基表 LOB 逻辑读取 1314
ARITHABORT OFF ✓ 命中 — ★ 与老版本行为不同！
────────────────────────────────────────────────────────────────────
两个必须记住的结论：
① **ARITHABORT 已经不再是元凶**。SQL Server 2025 把该 SET 选项废弃，行为等同常 ON，
所以网上「ARITHABORT OFF 会导致索引视图失效」的排查清单在 2025 上会浪费时间。
真正要盯的是 **ANSI_WARNINGS** 和 **CONCAT_NULL_YIELDS_NULL** ——
而这两者恰恰经常被 ODBC 老驱动、部分 ORM 的连接初始化脚本关掉。
② 真正会让自动匹配失效的是三兄弟：**ANSI_WARNINGS、CONCAT_NULL_YIELDS_NULL、
NUMERIC_ROUNDABORT**（本机实测：三者任一不满足，逻辑读取就从 4 跳到 1314，且零报错）。
③ **生产报表请显式写 WITH (NOEXPAND)**。理由见 09.4：
不加提示 → 静默变慢；加了提示 → 直接报 1934 并点名是哪个 SET 选项，问题一眼可见。
==============================================================================================*/
PRINT '';
PRINT '### 09 SET 选项与自动匹配 ###';
PRINT '-- 09.1 逐项 SET 选项的自动匹配矩阵 --';
PRINT ' 重要前提：SET 选项必须在**当前批次里直接执行**才会生效。';
PRINT ' 若把 SET 放进 sp_executesql，它会在动态批结束时自动回滚，';
PRINT ' 于是你会「以为关了 ANSI_WARNINGS」，实际根本没关（本节特意避开这个坑）。';
DECLARE @res TABLE(seq int IDENTITY, 选项 nvarchar(40), 命中 bit, 逻辑读取 bigint);
DECLARE @u bit, @rr bigint, @cc bigint, @ee bigint;
/* --- 基线：全部 SET 选项正确 --- */
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @u OUTPUT;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rr OUTPUT, @cpu_us = @cc OUTPUT, @elapsed_ms = @ee OUTPUT;
INSERT @res(选项, 命中, 逻辑读取) VALUES (N'全部正确（基线）', @u, @rr);
PRINT ' ' + LEFT(N'全部正确（基线）' + SPACE(34), 34) + N'命中=' + CAST(@u AS varchar(2)) + N' 逻辑读取=' + CAST(@rr AS varchar(20));
/* --- ANSI_NULLS OFF --- */
SET ANSI_NULLS OFF;
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @u OUTPUT;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rr OUTPUT, @cpu_us = @cc OUTPUT, @elapsed_ms = @ee OUTPUT;
INSERT @res(选项, 命中, 逻辑读取) VALUES (N'ANSI_NULLS OFF', @u, @rr);
PRINT ' ' + LEFT(N'ANSI_NULLS OFF' + SPACE(34), 34) + N'命中=' + CAST(@u AS varchar(2)) + N' 逻辑读取=' + CAST(@rr AS varchar(20));
SET ANSI_NULLS ON;
/* --- ANSI_WARNINGS OFF（预期：静默退化） --- */
SET ANSI_WARNINGS OFF;
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @u OUTPUT;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rr OUTPUT, @cpu_us = @cc OUTPUT, @elapsed_ms = @ee OUTPUT;
INSERT @res(选项, 命中, 逻辑读取) VALUES (N'ANSI_WARNINGS OFF', @u, @rr);
PRINT ' ' + LEFT(N'ANSI_WARNINGS OFF' + SPACE(34), 34) + N'命中=' + CAST(@u AS varchar(2)) + N' 逻辑读取=' + CAST(@rr AS varchar(20));
SET ANSI_WARNINGS ON;
/* --- CONCAT_NULL_YIELDS_NULL OFF（预期：静默退化） --- */
SET CONCAT_NULL_YIELDS_NULL OFF;
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @u OUTPUT;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rr OUTPUT, @cpu_us = @cc OUTPUT, @elapsed_ms = @ee OUTPUT;
INSERT @res(选项, 命中, 逻辑读取) VALUES (N'CONCAT_NULL_YIELDS_NULL OFF', @u, @rr);
PRINT ' ' + LEFT(N'CONCAT_NULL_YIELDS_NULL OFF' + SPACE(34), 34) + N'命中=' + CAST(@u AS varchar(2)) + N' 逻辑读取=' + CAST(@rr AS varchar(20));
SET CONCAT_NULL_YIELDS_NULL ON;
/* --- NUMERIC_ROUNDABORT ON --- */
SET NUMERIC_ROUNDABORT ON;
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @u OUTPUT;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rr OUTPUT, @cpu_us = @cc OUTPUT, @elapsed_ms = @ee OUTPUT;
INSERT @res(选项, 命中, 逻辑读取) VALUES (N'NUMERIC_ROUNDABORT ON', @u, @rr);
PRINT ' ' + LEFT(N'NUMERIC_ROUNDABORT ON' + SPACE(34), 34) + N'命中=' + CAST(@u AS varchar(2)) + N' 逻辑读取=' + CAST(@rr AS varchar(20));
SET NUMERIC_ROUNDABORT OFF;
/* --- ARITHABORT OFF（SQL Server 2025：已不再影响） --- */
SET ARITHABORT OFF;
EXEC dbo.VI_PlanUses @proc = 'dbo.VI_Q_OrgSalesAgg', @obj = N'VW_OrgSalesAgg', @used = @u OUTPUT;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_OrgSalesAgg', @reads = @rr OUTPUT, @cpu_us = @cc OUTPUT, @elapsed_ms = @ee OUTPUT;
INSERT @res(选项, 命中, 逻辑读取) VALUES (N'ARITHABORT OFF', @u, @rr);
PRINT ' ' + LEFT(N'ARITHABORT OFF' + SPACE(34), 34) + N'命中=' + CAST(@u AS varchar(2)) + N' 逻辑读取=' + CAST(@rr AS varchar(20));
SET ARITHABORT ON;
SELECT 选项, 命中物化视图 = CASE 命中 WHEN 1 THEN N'✓' ELSE N'✗ 静默退化' END, 逻辑读取,
说明 = CASE 命中 WHEN 1 THEN N'自动匹配正常' ELSE N'无任何报错，直接扫 5,000,000 行' END
FROM @res ORDER BY seq;
DECLARE @hitWarnings bit = (SELECT 命中 FROM @res WHERE 选项 = N'ANSI_WARNINGS OFF');
DECLARE @hitConcat bit = (SELECT 命中 FROM @res WHERE 选项 = N'CONCAT_NULL_YIELDS_NULL OFF');
DECLARE @hitArith bit = (SELECT 命中 FROM @res WHERE 选项 = N'ARITHABORT OFF');
DECLARE @hitNulls bit = (SELECT 命中 FROM @res WHERE 选项 = N'ANSI_NULLS OFF');
DECLARE @hitNumeric bit = (SELECT 命中 FROM @res WHERE 选项 = N'NUMERIC_ROUNDABORT ON');
DECLARE @rdWarnings bigint = (SELECT 逻辑读取 FROM @res WHERE 选项 = N'ANSI_WARNINGS OFF');
DECLARE @rdConcat bigint = (SELECT 逻辑读取 FROM @res WHERE 选项 = N'CONCAT_NULL_YIELDS_NULL OFF');
DECLARE @rdBase bigint = (SELECT 逻辑读取 FROM @res WHERE 选项 = N'全部正确（基线）');
PRINT ' ★ ANSI_WARNINGS OFF → 命中=' + CAST(@hitWarnings AS varchar(2)) + N'，逻辑读取=' + CAST(@rdWarnings AS varchar(20)) + N'（基线 ' + CAST(@rdBase AS varchar(20)) + N'）';
PRINT ' ★ CONCAT_NULL_YIELDS_NULL OFF → 命中=' + CAST(@hitConcat AS varchar(2)) + N'，逻辑读取=' + CAST(@rdConcat AS varchar(20));
PRINT ' ★ NUMERIC_ROUNDABORT ON → 命中=' + CAST(@hitNumeric AS varchar(2)) + N'（同样会静默退化）';
PRINT ' ★ ARITHABORT OFF → 命中=' + CAST(@hitArith AS varchar(2)) + N'（SQL Server 2025 已不再影响）';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'09_ANSI_WARNINGS_OFF使自动匹配失效', CAST(@hitWarnings AS nvarchar(200)), N'0', CASE WHEN @hitWarnings = 0 THEN 1 ELSE 0 END),
(N'09_CONCAT_NULL_OFF使自动匹配失效', CAST(@hitConcat AS nvarchar(200)), N'0', CASE WHEN @hitConcat = 0 THEN 1 ELSE 0 END),
(N'09_ARITHABORT_OFF仍命中', CAST(@hitArith AS nvarchar(200)), N'1', CASE WHEN @hitArith = 1 THEN 1 ELSE 0 END),
(N'09_ANSI_NULLS_OFF仍命中', CAST(@hitNulls AS nvarchar(200)), N'1', CASE WHEN @hitNulls = 1 THEN 1 ELSE 0 END),
(N'09_NUMERIC_ROUNDABORT_ON使自动匹配失效', CAST(@hitNumeric AS nvarchar(200)), N'0', CASE WHEN @hitNumeric = 0 THEN 1 ELSE 0 END);
PRINT '-- 09.2 失效时的实际代价（STATISTICS IO 直观看一眼）--';
PRINT ' SET ANSI_WARNINGS OFF 后执行同一句聚合：';
SET ANSI_WARNINGS OFF;
SET STATISTICS IO ON;
SELECT f.OrgUnitId, SUM(f.AmountUsd) AS 销售额, COUNT_BIG(*) AS 明细行数
FROM rpt.OrgSalesFact f GROUP BY f.OrgUnitId;
SET STATISTICS IO OFF;
SET ANSI_WARNINGS ON;
PRINT ' SET ANSI_WARNINGS ON 恢复后执行同一句：';
SET STATISTICS IO ON;
SELECT f.OrgUnitId, SUM(f.AmountUsd) AS 销售额, COUNT_BIG(*) AS 明细行数
FROM rpt.OrgSalesFact f GROUP BY f.OrgUnitId;
SET STATISTICS IO OFF;
PRINT '-- 09.3 本会话当前 SET 选项自检（上线前自检脚本）--';
SELECT 选项 = v.opt, 当前值 = v.cur, 是否符合索引视图要求 = v.judge
FROM (VALUES
('ANSI_NULLS', CAST(SESSIONPROPERTY('ANSI_NULLS') AS varchar(10)),
CASE WHEN SESSIONPROPERTY('ANSI_NULLS') = 1 THEN 'OK' ELSE '★ 会导致失效' END),
('ANSI_PADDING', CAST(SESSIONPROPERTY('ANSI_PADDING') AS varchar(10)),
CASE WHEN SESSIONPROPERTY('ANSI_PADDING') = 1 THEN 'OK' ELSE '★ 会导致失效' END),
('ANSI_WARNINGS', CAST(SESSIONPROPERTY('ANSI_WARNINGS') AS varchar(10)),
CASE WHEN SESSIONPROPERTY('ANSI_WARNINGS') = 1 THEN 'OK' ELSE '★ 会导致失效' END),
('ARITHABORT', CAST(SESSIONPROPERTY('ARITHABORT') AS varchar(10)),
'SQL Server 2025 已废弃，不再影响'),
('CONCAT_NULL_YIELDS_NULL', CAST(SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS varchar(10)),
CASE WHEN SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') = 1 THEN 'OK' ELSE '★ 会导致失效' END),
('NUMERIC_ROUNDABORT', CAST(SESSIONPROPERTY('NUMERIC_ROUNDABORT') AS varchar(10)),
CASE WHEN SESSIONPROPERTY('NUMERIC_ROUNDABORT') = 0 THEN 'OK' ELSE '★ 会导致失效' END),
('QUOTED_IDENTIFIER', CAST(SESSIONPROPERTY('QUOTED_IDENTIFIER') AS varchar(10)),
CASE WHEN SESSIONPROPERTY('QUOTED_IDENTIFIER') = 1 THEN 'OK' ELSE '★ 会导致失效' END)
) AS v(opt, cur, judge);
PRINT '-- 09.4 NOEXPAND：把「静默变慢」变成「明确报错」--';
SET ANSI_WARNINGS OFF;
DECLARE @neErr int = 0, @neMsg nvarchar(400) = N'';
BEGIN TRY
EXEC sp_executesql N'SELECT OrgUnitId, SUM(SalesUsd) AS S FROM dbo.VW_OrgSalesAgg WITH (NOEXPAND) GROUP BY OrgUnitId;';
END TRY
BEGIN CATCH
SET @neErr = ERROR_NUMBER();
SET @neMsg = ERROR_MESSAGE();
END CATCH
SET ANSI_WARNINGS ON;
PRINT ' ANSI_WARNINGS OFF + NOEXPAND => 错误 ' + CAST(@neErr AS varchar(10));
PRINT ' ' + LEFT(@neMsg, 220);
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'09_NOEXPAND加坏SET报1934', CAST(@neErr AS nvarchar(200)), N'1934', CASE WHEN @neErr = 1934 THEN 1 ELSE 0 END);
PRINT '-- 09.5 NOEXPAND 只能用在真的有索引的视图上 --';
DECLARE @plainErr int = 0;
BEGIN TRY
EXEC sp_executesql N'SELECT COUNT(*) FROM dbo.vw_SalesDetail WITH (NOEXPAND);';
END TRY
BEGIN CATCH
SET @plainErr = ERROR_NUMBER();
PRINT ' 普通视图 + NOEXPAND => 错误 ' + CAST(@plainErr AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 140);
END CATCH
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'09_普通视图用NOEXPAND报8171', CAST(@plainErr AS nvarchar(200)), N'8171', CASE WHEN @plainErr = 8171 THEN 1 ELSE 0 END);
PRINT '-- 09.6 工程建议：把 SET 选项写进连接串 / 存储过程头部 --';
PRINT ' · 存储过程头部显式声明 7 个 SET（模块级 SET 选项会随模块一起缓存）；';
PRINT ' · ODBC/JDBC 连接串显式设置 ansi_warnings=on、"CONCAT_NULL_YIELDS_NULL=ON"；';
PRINT ' · 报表/聚合视图统一写 WITH (NOEXPAND)；';
PRINT ' · 用本节的 09.3 自检查询挂到健康检查里。';
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
10 写入放大与数据新鲜度：索引视图的账单
────────────────────────────────────────────────────────────────────────────────────────────
第 08 节证明了读侧的巨大收益。这一节必须把账单摊开讲——索引视图不是免费的查询缓存，
它是**基表的一部分**：每一次 DML 都要在同一事务内把物化结果改对。
本机实测（坑 10）：dbo.VI_Base 200,000 行，单行 UPDATE
无聚合索引视图：逻辑读取 3
有聚合索引视图：VI_BaseAgg 6 + Worktable 14（聚合重算） + VI_Base 3 = 23
约 7.7 倍写放大，并额外出现 Worktable 算子
更关键的是**并发**层面：
所有落到同一聚合组（同一 OrgUnitId）的并发写入，都要去改**同一行**物化记录。
这在万亿级写入下会形成新的热点键，用行锁排队。
数据新鲜度对照：
索引视图 = 事务内同步 → 强一致，无「刷新」概念，但写入路径变长、锁窗口变宽；
预聚合表 = 作业异步刷新 → 最终一致，可控制延迟与刷新窗口，读写彻底解耦。
==============================================================================================*/
PRINT '';
PRINT '### 10 写入放大与数据新鲜度 ###';
PRINT '-- 10.1 建立被测过程：单行 UPDATE（幂等写法，重复运行值不变）--';
GO
CREATE OR ALTER PROCEDURE dbo.VI_Q_BaseUpdate AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.VI_Base SET AmountUsd = 1000.0000 WHERE Id = 7;
END
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT '-- 10.2 场景 A：没有任何索引视图引用 VI_Base --';
IF OBJECT_ID('dbo.VI_BaseAgg', 'V') IS NOT NULL DROP VIEW dbo.VI_BaseAgg;
DECLARE @rA bigint, @cA bigint, @eA bigint;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_BaseUpdate', @reads = @rA OUTPUT, @cpu_us = @cA OUTPUT, @elapsed_ms = @eA OUTPUT;
PRINT ' 无索引视图：逻辑读取 = ' + CAST(@rA AS varchar(20)) + N' CPU(us) = ' + CAST(@cA AS varchar(20));
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过)
VALUES (N'10_场景A无索引视图UPDATE读取', CAST(@rA AS nvarchar(200)), N'<场景B', 1);
PRINT ' STATISTICS IO 明细：';
SET STATISTICS IO ON;
UPDATE dbo.VI_Base SET AmountUsd = 1000.0000 WHERE Id = 7;
SET STATISTICS IO OFF;
PRINT '-- 10.3 场景 B：建一个按组织单元聚合的索引视图，再做同一句 UPDATE --';
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO
GO
CREATE VIEW dbo.VI_BaseAgg WITH SCHEMABINDING AS
SELECT b.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS 明细行数, SUM(b.AmountUsd) AS 销售额
FROM dbo.VI_Base AS b
GROUP BY b.OrgUnitId;
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
CREATE UNIQUE CLUSTERED INDEX IXC_VI_BaseAgg ON dbo.VI_BaseAgg(OrgUnitId);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
DECLARE @rA2 bigint = TRY_CAST((SELECT 实测值 FROM dbo.VI_ResultCheck
WHERE 检查项 = N'10_场景A无索引视图UPDATE读取') AS bigint);
DECLARE @rB bigint, @cB bigint, @eB bigint;
EXEC dbo.VI_Bench @proc = 'dbo.VI_Q_BaseUpdate', @reads = @rB OUTPUT, @cpu_us = @cB OUTPUT, @elapsed_ms = @eB OUTPUT;
PRINT ' 有索引视图：逻辑读取 = ' + CAST(@rB AS varchar(20)) + N' CPU(us) = ' + CAST(@cB AS varchar(20));
PRINT ' STATISTICS IO 明细（注意多出来的 VI_BaseAgg 与 Worktable）：';
SET STATISTICS IO ON;
UPDATE dbo.VI_Base SET AmountUsd = 1000.0000 WHERE Id = 7;
SET STATISTICS IO OFF;
DECLARE @amp decimal(10,2) = CASE WHEN @rA2 > 0 THEN CAST(@rB AS decimal(10,2)) / @rA2 ELSE 0 END;
PRINT ' 写放大倍数 = ' + CAST(@amp AS varchar(20)) + N' 倍';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'10_单行UPDATE写放大倍数', CAST(@amp AS nvarchar(200)), N'>=3', CASE WHEN @amp >= 3 THEN 1 ELSE 0 END),
(N'10_有索引视图时UPDATE读取更高', CAST(@rB AS nvarchar(200)), N'>' + CAST(@rA2 AS nvarchar(20)), CASE WHEN @rB > @rA2 THEN 1 ELSE 0 END);
PRINT '-- 10.4 并发热点：同一聚合组的写入会争用同一行物化记录 --';
BEGIN TRANSACTION;
UPDATE dbo.VI_Base SET AmountUsd = 1000.0000 WHERE Id = 7;
PRINT ' 当前事务持有的锁（按对象/索引聚合）：';
SELECT 对象 = ISNULL(OBJECT_NAME(p.object_id), N'(其他)'),
索引 = ISNULL(i.name, N'-'),
锁类型 = l.resource_type,
锁模式 = l.request_mode,
锁数量 = COUNT(*)
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE l.request_session_id = @@SPID
GROUP BY OBJECT_NAME(p.object_id), i.name, l.resource_type, l.request_mode
ORDER BY 1, 2;
PRINT ' ↑ 一次单行 UPDATE 同时锁住了基表行**和**物化视图的行——';
PRINT ' 这就是高并发写入下「读加速」的隐藏成本。';
ROLLBACK TRANSACTION;
PRINT ' 事务已回滚（本次演示不留副作用）。';
PRINT '-- 10.5 数据新鲜度：索引视图是事务内同步，不存在「刷新延迟」--';
DECLARE @before decimal(18,4) = (SELECT 销售额 FROM dbo.VI_BaseAgg WHERE OrgUnitId = (SELECT OrgUnitId FROM dbo.VI_Base WHERE Id = 7));
BEGIN TRANSACTION;
UPDATE dbo.VI_Base SET AmountUsd = AmountUsd + 100 WHERE Id = 7;
DECLARE @inside decimal(18,4) = (SELECT 销售额 FROM dbo.VI_BaseAgg WITH (NOEXPAND) WHERE OrgUnitId = (SELECT OrgUnitId FROM dbo.VI_Base WHERE Id = 7));
PRINT ' 事务内：同一事务里改基表后，物化视图立即反映 → ' + CAST(@before AS varchar(30)) + N' → ' + CAST(@inside AS varchar(30));
ROLLBACK TRANSACTION;
DECLARE @after decimal(18,4) = (SELECT 销售额 FROM dbo.VI_BaseAgg WHERE OrgUnitId = (SELECT OrgUnitId FROM dbo.VI_Base WHERE Id = 7));
PRINT ' 回滚后：物化视图同步回退 → ' + CAST(@after AS varchar(30)) + N'（与事务前一致）';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'10_索引视图事务内即时一致', CASE WHEN @inside <> @before AND @after = @before THEN N'一致' ELSE N'不一致' END,
N'一致', CASE WHEN @inside <> @before AND @after = @before THEN 1 ELSE 0 END);
PRINT '-- 10.6 新鲜度策略对照（万亿级选型）--';
SELECT 方案 = v.方案, 一致性 = v.一致性, 刷新动作 = v.刷新, 写入代价 = v.写代价, 适用 = v.适用
FROM (VALUES
('索引视图（物化视图）', N'强一致（事务内同步）', N'无需刷新', N'每次 DML 同步维护，实测 7.7×', N'读多写少的维表/汇总，TB 级以内'),
('预聚合表 + 增量作业', N'最终一致（可设延迟）', N'定时/事件触发增量', N'由作业承担，读写解耦', N'万亿级事实表的分钟级汇总 ★'),
('分区对齐索引视图', N'强一致', N'只需维护热分区', N'仅热分区承担写放大', N'按时间滚动、需实时一致的场景'),
('列存 + 按需聚合', N'强一致', N'无需刷新', N'写入即列存批量压缩', N'明细量大但维度基数低时最优')
) AS v(方案, 一致性, 刷新, 写代价, 适用);
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
11 万亿级高并发：索引视图必须分区对齐，否则归档通道被堵死
────────────────────────────────────────────────────────────────────────────────────────────
万亿级数据表在物理上一定是**按时间分区**的。冷数据出库（归档 / 转冷存储 / 只读留存）
在 SQL Server 里靠 `ALTER TABLE ... SWITCH PARTITION` 完成 —— 它是**元数据操作**，
几 TB 的分区切换是秒级；换成 DELETE 就是几小时 + 巨大的日志量。
而索引视图能在这一步上直接把你卡死（坑 11，本机实测）：
索视图的索引建在默认文件组（未分区）时：
ALTER TABLE dbo.VI_Part SWITCH PARTITION 2 TO dbo.VI_PartStage PARTITION 2
→ 报错 11401：「表 'VI_Part' 为已分区，但索引视图 'VI_PartAgg' 的索引
'IXC_VI_PartAgg' 为未分区」
把同一个索引改成 ON ps_vipart(Dt)（对齐到同一分区方案）后：
→ SWITCH PARTITION **成功**
另外两个必须知道的连带限制（同样实测）：
TRUNCATE TABLE dbo.VI_Part WITH (PARTITIONS (3)) → **3729**（即使分区已对齐）
ALTER TABLE dbo.VI_Part ALTER COLUMN Amt ... → **4922**
SCHEMABINDING 锁住的不只是执行计划，还有基表的 DDL 与批量清理能力。
==============================================================================================*/
PRINT '';
PRINT '### 11 万亿级高并发：分区对齐 ###';
PRINT '-- 11.1 建立分区表之上的索引视图（索引先建在默认文件组）--';
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO
GO
CREATE VIEW dbo.VI_PartAgg WITH SCHEMABINDING AS
SELECT p.Dt AS Dt, p.OrgUnitId AS OrgUnitId, COUNT_BIG(*) AS 明细行数, SUM(p.Amt) AS 金额
FROM dbo.VI_Part AS p
GROUP BY p.Dt, p.OrgUnitId;
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
CREATE UNIQUE CLUSTERED INDEX IXC_VI_PartAgg ON dbo.VI_PartAgg(Dt, OrgUnitId);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
PRINT ' 当前索引所在数据空间：';
SELECT 索引 = i.name, 数据空间 = ds.name, 空间类型 = ds.type_desc,
是否分区对齐 = CASE WHEN ds.type = 'PS' THEN N'已对齐' ELSE N'未对齐 ★' END
FROM sys.indexes i JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('dbo.VI_PartAgg');
PRINT '-- 11.2 未分区对齐时执行分区切换（归档冷数据）--';
DECLARE @sw1 int = 0;
BEGIN TRY
EXEC sp_executesql N'ALTER TABLE dbo.VI_Part SWITCH PARTITION 2 TO dbo.VI_PartStage PARTITION 2;';
PRINT ' => 竟然成功';
END TRY
BEGIN CATCH
SET @sw1 = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@sw1 AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 220);
END CATCH
PRINT ' ↑ 注意：这与「数据对不对」无关，纯粹是元数据结构不匹配。';
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'11_未对齐索引视图阻塞SWITCH', CAST(@sw1 AS nvarchar(200)), N'11401', CASE WHEN @sw1 = 11401 THEN 1 ELSE 0 END);
PRINT '-- 11.3 把索引对齐到同一分区方案，SWITCH 立刻恢复 --';
DROP INDEX IXC_VI_PartAgg ON dbo.VI_PartAgg;
CREATE UNIQUE CLUSTERED INDEX IXC_VI_PartAgg ON dbo.VI_PartAgg(Dt, OrgUnitId) ON ps_vipart(Dt);
PRINT ' 已重建为 ON ps_vipart(Dt)';
SELECT 索引 = i.name, 数据空间 = ds.name, 空间类型 = ds.type_desc,
分区数 = (SELECT COUNT(*) FROM sys.partitions p WHERE p.object_id = i.object_id AND p.index_id = i.index_id)
FROM sys.indexes i JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('dbo.VI_PartAgg');
DECLARE @sw2 int = 0;
BEGIN TRY
EXEC sp_executesql N'ALTER TABLE dbo.VI_Part SWITCH PARTITION 2 TO dbo.VI_PartStage PARTITION 2;';
PRINT ' => SWITCH PARTITION 成功（元数据级切换，秒级完成）';
END TRY
BEGIN CATCH
SET @sw2 = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@sw2 AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 220);
END CATCH
PRINT ' 切换后的分区分布（源表分区 2 已被清空，目标表分区 2 接管）：';
SELECT 表 = OBJECT_NAME(p.object_id), 分区号 = p.partition_number, 行数 = p.rows
FROM sys.partitions p
WHERE p.object_id IN (OBJECT_ID('dbo.VI_Part'), OBJECT_ID('dbo.VI_PartStage')) AND p.index_id = 1
ORDER BY 1, 2;
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'11_分区对齐后SWITCH成功', CAST(@sw2 AS nvarchar(200)), N'0', CASE WHEN @sw2 = 0 THEN 1 ELSE 0 END);
PRINT '-- 11.4 即使对齐，TRUNCATE 仍被索引视图阻塞 --';
DECLARE @truncErr int = 0;
BEGIN TRY
EXEC sp_executesql N'TRUNCATE TABLE dbo.VI_Part WITH (PARTITIONS (3));';
PRINT ' => 成功';
END TRY
BEGIN CATCH
SET @truncErr = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@truncErr AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 200);
END CATCH
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'11_TRUNCATE被索引视图阻塞', CAST(@truncErr AS nvarchar(200)), N'3729', CASE WHEN @truncErr = 3729 THEN 1 ELSE 0 END);
PRINT '-- 11.5 SCHEMABINDING 锁住基表列定义 --';
DECLARE @alterErr int = 0;
BEGIN TRY
EXEC sp_executesql N'ALTER TABLE dbo.VI_Part ALTER COLUMN Amt decimal(20,6) NOT NULL;';
PRINT ' => 成功';
END TRY
BEGIN CATCH
SET @alterErr = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@alterErr AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 200);
END CATCH
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'11_索引视图阻塞ALTER COLUMN', CAST(@alterErr AS nvarchar(200)), N'4922', CASE WHEN @alterErr = 4922 THEN 1 ELSE 0 END);
PRINT '-- 11.6 万亿级：分区对齐 + 滚动窗口的完整范式 --';
PRINT ' ① 事实表按 OrderDate 做 RANGE RIGHT 分区（按月/周），热分区在线，冷分区只读；';
PRINT ' ② 索引视图 GROUP BY (分区键, 分析维度)，并**用 ON <同一分区方案>(分区键) 建索引**；';
PRINT ' ③ 日常只写热分区 → 物化维护成本被限制在热分区内；';
PRINT ' ④ 冷数据出库用 ALTER TABLE ... SWITCH PARTITION（秒级元数据操作）；';
PRINT ' ⑤ 需要 TRUNCATE / 改列时，先 DROP 索引视图 → 操作 → 重建（把它纳入变更流程）。';
PRINT '';
PRINT ' 参考模板（示例，不实际执行）：';
PRINT ' CREATE VIEW rpt.VW_SalesByMonthOrg WITH SCHEMABINDING AS';
PRINT ' SELECT f.OrderDate, f.OrgUnitId, f.CategoryId,';
PRINT ' COUNT_BIG(*) AS OrderCnt, SUM(f.AmountUsd) AS SalesUsd';
PRINT ' FROM dbo.FactSales f';
PRINT ' GROUP BY f.OrderDate, f.OrgUnitId, f.CategoryId;';
PRINT ' CREATE UNIQUE CLUSTERED INDEX IXC_VW_SalesByMonthOrg';
PRINT ' ON rpt.VW_SalesByMonthOrg(OrderDate, OrgUnitId, CategoryId)';
PRINT ' ON ps_orderdate(OrderDate); -- ★ 分区对齐，缺这句就没有 ④';
PRINT '-- 11.7 索引视图的索引支持 ONLINE 重建（万亿级不停机维护）--';
DECLARE @onErr int = 0;
BEGIN TRY
ALTER INDEX IXC_VW_OrgSalesAgg ON dbo.VW_OrgSalesAgg REBUILD WITH (ONLINE = ON);
PRINT ' => ONLINE 重建索引视图成功（可在业务高峰执行）';
END TRY
BEGIN CATCH
SET @onErr = ERROR_NUMBER();
PRINT ' => 错误 ' + CAST(@onErr AS varchar(10)) + ' : ' + LEFT(ERROR_MESSAGE(), 180);
END CATCH
INSERT dbo.VI_ResultCheck(检查项, 实测值, 期望, 是否通过) VALUES
(N'11_索引视图支持ONLINE重建', CASE WHEN @onErr = 0 THEN N'0' ELSE CAST(@onErr AS nvarchar(200)) END,
N'0', CASE WHEN @onErr = 0 THEN 1 ELSE 0 END);
PRINT '-- 11.8 高并发下的注意事项清单 --';
SELECT 关注点 = v.t, 说明 = v.d
FROM (VALUES
(N'热点聚合行争用', N'同一 OrgUnitId 的并发写入会争用同一行物化记录；分区对齐 + 按更细维度分组可打散'),
(N'写放大', N'实测单行 UPDATE 逻辑读取 3 → 23；批量导入前评估索引视图数量，宁少勿多'),
(N'锁窗口', N'物化维护在同一事务内，长事务会拉长基表行锁持有时间'),
(N'参数嗅探', N'索引视图不改变参数嗅探，但改变可用的计划空间；配合 OPTION (RECOMPILE) 慎用'),
(N'统计信息', N'索引视图的聚集索引自带统计对象（sys.stats 可见），但仍需关注基表统计'),
(N'计划缓存膨胀', N'不同 SET 选项组合会生成不同计划条目，连接层统一 SET 可显著减少条目数'),
(N'在线重建', N'ONLINE=ON 可用，但会带来额外的版本存储与日志，需要在低峰或分批执行'),
(N'变更流程', N'基表 DDL / TRUNCATE / 大批删 需与索引视图的 DROP→REBUILD 一起排期')
) AS v(t, d);
GO
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
12 运行时诊断与决策清单
==============================================================================================*/
PRINT '';
PRINT '### 12 运行时诊断与决策清单 ###';
PRINT '-- 12.1 视图盘点：哪些是普通视图、哪些已经物化 --';
SELECT 视图 = s.name + '.' + v.name,
绑定架构 = CASE WHEN m.is_schema_bound = 1 THEN N'是' ELSE N'否' END,
索引数 = (SELECT COUNT(*) FROM sys.indexes i WHERE i.object_id = v.object_id AND i.index_id > 0),
物化状态 = CASE WHEN EXISTS (SELECT 1 FROM sys.indexes i
WHERE i.object_id = v.object_id AND i.index_id > 0 AND i.is_unique = 1
AND i.type IN (1, 2))
THEN N'已物化（索引视图）' ELSE N'逻辑视图（不存数据）' END,
创建时间 = v.create_date,
修改时间 = v.modify_date
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
LEFT JOIN sys.sql_modules m ON m.object_id = v.object_id
WHERE s.name = 'dbo'
ORDER BY 3 DESC, 1;
PRINT '-- 12.2 判断索引视图是否真的在被业务使用 --';
PRINT ' 主判定：执行计划里是否出现该物化视图的名字（见 08 节的方法）；';
PRINT ' 辅助参考：sys.dm_db_index_usage_stats（注意：优化器在**考虑**自动匹配时也会触碰它，';
PRINT ' 所以它只能当参考，不能当结论）。';
SELECT 对象 = OBJECT_NAME(us.object_id),
索引 = i.name,
用户查找 = us.user_seeks,
用户扫描 = us.user_scans,
用户更新 = us.user_updates,
最后扫描 = us.last_user_scan,
结论 = CASE WHEN ISNULL(us.user_seeks,0) + ISNULL(us.user_scans,0) = 0
THEN N'★ 从未被读取，却在持续收写入账单 → 建议评估下线'
ELSE N'有读取，收益需与写放大权衡' END
FROM sys.dm_db_index_usage_stats us
JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id
WHERE us.database_id = DB_ID()
AND us.object_id IN (SELECT v.object_id FROM sys.views v
WHERE EXISTS (SELECT 1 FROM sys.indexes x
WHERE x.object_id = v.object_id AND x.index_id > 0));
PRINT '-- 12.3 视图元数据健康检查 --';
PRINT ' (a) 引用对象已不存在的视图（会出现「列名无效」）：';
SELECT 视图 = OBJECT_SCHEMA_NAME(d.referencing_id) + '.' + OBJECT_NAME(d.referencing_id),
缺失对象 = d.referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id IS NULL
AND OBJECTPROPERTY(d.referencing_id, 'IsView') = 1;
PRINT ' (b) 未绑定架构、且列清单可能已过期的视图（建议定期 sp_refreshview）：';
SELECT 视图 = s.name + '.' + v.name, 列数 = COUNT(c.column_id)
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
LEFT JOIN sys.sql_modules m ON m.object_id = v.object_id
LEFT JOIN sys.columns c ON c.object_id = v.object_id
WHERE (m.is_schema_bound = 0 OR m.is_schema_bound IS NULL) AND s.name = 'dbo'
GROUP BY s.name + '.' + v.name
ORDER BY 2 DESC, 1;
PRINT '-- 12.4 依赖目录：查出「谁引用了这张基表」--';
PRINT ' 改基表结构 / 砍列之前的必查项：';
SELECT 引用方类型 = o.type_desc,
引用方 = OBJECT_SCHEMA_NAME(d.referencing_id) + '.' + OBJECT_NAME(d.referencing_id),
被引用对象 = d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON o.object_id = d.referencing_id
WHERE d.referenced_entity_name = 'SalesOrder'
ORDER BY 1, 2;
PRINT '-- 12.5 决策清单：什么时候用视图、什么时候用索引视图 --';
SELECT 场景 = v.场景, 建议方案 = v.方案, 理由 = v.理由
FROM (VALUES
(N'屏蔽复杂 JOIN、统一业务口径', N'普通视图', N'零运行时开销（本机实测与直接 JOIN 读取次数相同）'),
(N'列级/行级授权、隐藏成本价等敏感列', N'普通视图 + GRANT', N'所有权链生效，实测「视图可查、基表 229」'),
(N'允许前端改数据但限制范围', N'视图 + WITH CHECK OPTION', N'不加就静默丢可见性（实测 550 vs 静默消失）'),
(N'读多写少的维度汇总（<1 亿行）', N'索引视图', N'读侧 324× 收益，写侧 7.7× 代价可接受'),
(N'写多读多、分钟级延迟可接受', N'预聚合表 + 增量作业', N'读写解耦，刷新时机可控'),
(N'万亿级明细的实时汇总', N'分区对齐索引视图 + 列存', N'物化只维护热分区，冷分区 SWITCH 出库'),
(N'需要窗口函数/行号计算的展现层', N'视图里不放窗口函数', N'实测 ROW_NUMBER 使下推失效，读取放大 973×'),
(N'基表结构频繁变更（加列/改类型）', N'避免 SCHEMABINDING', N'SCHEMABINDING 会阻塞 ALTER COLUMN（4922）与 TRUNCATE（3729）'),
(N'视图里需要 CTE 做中间层', N'不要放进索引视图', N'视图内 CTE 无法建索引（10137）'),
(N'生产报表查询索引视图', N'显式 WITH (NOEXPAND)', N'要么走物化，要么报 1934 点名 SET 选项，不要静默降级')
) AS v(场景, 方案, 理由);
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
13 自检：逐条断言
==============================================================================================*/
PRINT '';
PRINT '### 13 自检：逐条断言 ###';
SELECT 序号 = ROW_NUMBER() OVER (ORDER BY 检查项),
检查项, 实测值, 期望,
结果 = CASE WHEN 是否通过 = 1 THEN N'PASS' ELSE N'**FAIL**' END
FROM dbo.VI_ResultCheck ORDER BY 检查项;
DECLARE @total int = (SELECT COUNT(*) FROM dbo.VI_ResultCheck);
DECLARE @pass int = (SELECT COUNT(*) FROM dbo.VI_ResultCheck WHERE 是否通过 = 1);
DECLARE @fail int = (SELECT COUNT(*) FROM dbo.VI_ResultCheck WHERE 是否通过 = 0);
PRINT '';
PRINT ' 自检项总数 : ' + CAST(@total AS varchar(10));
PRINT ' 通过 : ' + CAST(@pass AS varchar(10));
PRINT ' 失败 : ' + CAST(@fail AS varchar(10));
IF @fail = 0
PRINT ' >>> 全部自检通过。 <<<';
ELSE
BEGIN
PRINT ' >>> 存在失败项，明细如下： <<<';
SELECT 检查项, 实测值, 期望 FROM dbo.VI_ResultCheck WHERE 是否通过 = 0;
END
PRINT '';
PRINT ' 索引视图限制矩阵复核（18 个场景：3 个建索引成功 / 15 个报错）：';
SELECT 结论分布 = 结论, 数量 = COUNT(*) FROM dbo.VI_RestrictionCheck GROUP BY 结论 ORDER BY 1;
GO
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
/*==============================================================================================
14 总结
==============================================================================================*/
PRINT '';
PRINT '==============================================================================================';
PRINT ' 总结：SQL Server 2025 视图与物化视图 —— 15 条可落地的结论';
PRINT '==============================================================================================';
PRINT ' 1. SQL Server 没有 CREATE MATERIALIZED VIEW（实测报 102）。物化只能靠索引视图：';
PRINT ' WITH SCHEMABINDING 视图 + 唯一聚集索引。';
PRINT ' 2. 视图是宏，不是表：三层嵌套视图与等价直接 JOIN 的读取次数完全相同（实测 Product 4 /';
PRINT ' SalesOrderLine 74 / SalesOrder 2），运行时零额外开销，成本只在编译期展开。';
PRINT ' 3. 视图是访问控制边界：只授视图即可查询（实测 11,296 行），直接查基表被拒（实测 229）。';
PRINT ' 4. 视图像元数据是创建时快照：基表加列后 SELECT * 视图不报错也不更新，必须 sp_refreshview。';
PRINT ' 而删列后该视图会变成「坏视图」，直到有人查它才报 207（实测）。';
PRINT ' 5. 与常见说法相反：非绑定架构的普通视图**拦不住**基表 DROP COLUMN（实测成功），';
PRINT ' 6. 可更新视图不加 WITH CHECK OPTION 会静默丢可见性：越界 UPDATE 成功 1 行，视图可见数 1→0；';
PRINT ' 加了才报 550。含聚合的视图不可更新，实测 4406。';
PRINT ' 7. 视图里放 ROW_NUMBER() 或聚合会让外层谓词失去下推：实测逻辑读取 3 → 2919（973 倍）；';
PRINT ' 窗口函数要放到最外层。';
PRINT ' 8. 「TOP 100 PERCENT 会阻断下推」在 SQL Server 2025 上已不成立（实测仍是 3 次读取）。';
PRINT ' 9. 索引视图的 15 类限制在 CREATE VIEW 时不检查，到 CREATE INDEX 才炸：';
PRINT ' 10136 / 10113 / 10116 / 10100 / 10127 / 1949 / 10125 / 1939 / 10137 / 10140 /';
PRINT ' 1947 / 1941 / 10109 / 8662（SUM 的可空表达式）；18 个实测场景中仅 3 个允许建索引。';
PRINT ' 10. 好消息：视图可以建在 5,000,000 行的列存基表之上并成功物化（实测 4 页 / 32 KB / 210 行）。';
PRINT ' 11. 优化器会自动匹配索引视图：同一句基表聚合 SQL，物化前 LOB 读 1296，物化后逻辑读 4（324×）；';
PRINT ' 但一个被遗忘的索引视图也会悄悄改写基表查询的计划。';
PRINT ' 12. 自动匹配失效时完全静默：ANSI_WARNINGS OFF → 2682 读；CONCAT_NULL_YIELDS_NULL OFF → 1296 读；';
PRINT ' 两者都不报错。加 WITH (NOEXPAND) 才会报 1934 并点名是哪个 SET 选项。';
PRINT ' 13. ★ 行为更新：ARITHABORT OFF 在 SQL Server 2025 已不再导致自动匹配失效（实测仍命中）；';
PRINT ' ANSI_NULLS OFF 同样仍命中。失效的真凶是 ANSI_WARNINGS / CONCAT_NULL_YIELDS_NULL /';
PRINT ' NUMERIC_ROUNDABORT 三者任一不满足（实测逻辑读取 4 → 1314，零报错）。';
PRINT ' 14. 写放大实测 7.7×（单行 UPDATE 逻辑读取 3 → 23，并新增 Worktable 聚合重算），';
PRINT ' 且同一聚合组的并发写入争用同一行物化记录。索引视图要「宁少勿多」。';
PRINT ' 15. 万亿级的生死线是**分区对齐**：索引视图的索引不分区时 SWITCH PARTITION 报 11401；';
PRINT ' 改成 ON <同一分区方案>(分区键) 后立即成功。此外 TRUNCATE 报 3729、ALTER COLUMN 报 4922，';
PRINT ' 索引视图必须纳入基表变更流程。';
PRINT '';
PRINT ' 一句话选型：';
PRINT ' 抽象 + 授权 → 普通视图（零成本，必做）';
PRINT ' 实时一致的汇总 → 索引视图（读多写少，注意 SET 选项与 NOEXPAND）';
PRINT ' 万亿级准实时汇总 → 分区对齐索引视图 或 预聚合表 + 增量刷新';
PRINT ' 展现层行号/排名 → 放最外层 SELECT，不要埋进视图';
PRINT '==============================================================================================';
PRINT ' 演示结束。';
PRINT '==============================================================================================';
GO
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号