用索引视图实现大表的快速更新

原贴：点击打开链接

USE tempdb
GO
IF OBJECT_ID('view_test') IS NOT NULL
	DROP VIEW view_test
GO
IF OBJECT_ID('father') IS NOT NULL DROP TABLE father
IF OBJECT_ID('sub') IS NOT NULL DROP TABLE sub
GO
CREATE TABLE father(
	fid varchar(20),
	allOK BIT	
)
GO
CREATE TABLE sub(
	fid VARCHAR(20),
	rid INT,
    subOK BIT
)
GO
SET NOCOUNT ON
INSERT INTO father VALUES('001',0)
INSERT INTO father VALUES('002',0)
--
INSERT INTO sub VALUES ('001',1,1)
INSERT INTO sub VALUES ('001',2,1)
INSERT INTO sub VALUES ('002',1,1)
INSERT INTO sub VALUES ('002',2,0)
GO
--1. 创建索引视图，这个相当于统计好了结果，并存入到了一张物理表，后面再来查找是相当快的。
IF OBJECT_ID('view_test') IS NOT NULL
	DROP VIEW view_test
GO
CREATE VIEW dbo.view_test WITH SCHEMABINDING
AS
SELECT a.fid,COUNT_BIG(*) AS cnt,SUM(CASE WHEN a.subOK=1 THEN 0 ELSE 1 END) AS allOkEquals0 FROM dbo.sub AS a GROUP BY a.fid
GO
--2. 创建视图的索引
CREATE UNIQUE CLUSTERED INDEX ix_view_test ON dbo.view_test (fid);
GO
--3. 查看索引视图的内容
SELECT * FROM view_test;
--4. 更新
UPDATE f
SET allOK=1
FROM father AS f INNER JOIN view_test AS v ON f.fid=v.fid AND f.allOK=0 AND v.allOkEquals0=0
--5. 查看更新后的结果
SELECT * FROM father AS f