使用索引视图的过程
今天使用了索引视图,建起来还是真麻烦:代码
必须用Inner的内连接方式,需要绑定框架
1
SET ANSI_NULLS ON
2SET ANSI_PADDING ON
3SET ANSI_WARNINGS ON
4SET CONCAT_NULL_YIELDS_NULL ON
5SET NUMERIC_ROUNDABORT OFF
6SET QUOTED_IDENTIFIER ON
7SET ARITHABORT ON
8GO
9
10CREATE VIEW WHBTest_01 WITH SCHEMABINDING AS
11SELECT a.ProductionChildID, a.ProductionStateID, a.ProductionChildNumber, a.MinAssignDateTime, a.DesignTaskCompleteDateTime,
12 a.DoTaskCompleteDateTime, a.Workload AS Workload_one, a.BalanceWorkload, a.GotupCalcWorkload, a.PerformanceWorkload, a.QualityModulus,
13 a.LastEditTime, a.NaturalPromulgateTime AS MaxPromulgateTime, 20 AS WorkingDays, a.PEStandardTypeID, a.StopOrRestart, b.ContractID,
14 b.ProductionMainID, b.ClientID, b.Clientcnm, b.ContractNumber, c.RealAmount, c.SalerAreaId AS AreaID, c.ChildContractNumber, c.ContractDate,
15 c.SalerAreaName AS AreaName, d.IsRenew, d.IsAllUse, d.AcceptAllOrderValidateID, d.AverageQualityModulus, e.PEStandardTypeName
16FROM dbo.WEBT_ProductionChild AS a INNER JOIN
17 dbo.ProductionMain AS b ON a.ProductionStateID IN (3, 4, 5, 6, 8, 71, 72, 73, 74, 75, 76, 77) AND a.ProductionMainID = b.ProductionMainID INNER JOIN
18 dbo.CSLT_Contract AS c ON b.ContractID = c.ContractID INNER JOIN
19 dbo.WEBT_ProductionDetail AS d ON a.ProductionMainID = d.ProductionMainID INNER JOIN
20 dbo.WEBD_PEStandardType AS e ON a.PEStandardTypeID = e.PEStandardTypeID
21GO
22CREATE UNIQUE CLUSTERED INDEX V_Index_ProductionChildID ON WHBTest_01(ProductionChildID)
23
24
SET ANSI_NULLS ON 2
SET ANSI_PADDING ON 3
SET ANSI_WARNINGS ON 4
SET CONCAT_NULL_YIELDS_NULL ON 5
SET NUMERIC_ROUNDABORT OFF 6
SET QUOTED_IDENTIFIER ON 7
SET ARITHABORT ON 8
GO9
10
CREATE VIEW WHBTest_01 WITH SCHEMABINDING AS11
SELECT a.ProductionChildID, a.ProductionStateID, a.ProductionChildNumber, a.MinAssignDateTime, a.DesignTaskCompleteDateTime, 12
a.DoTaskCompleteDateTime, a.Workload AS Workload_one, a.BalanceWorkload, a.GotupCalcWorkload, a.PerformanceWorkload, a.QualityModulus, 13
a.LastEditTime, a.NaturalPromulgateTime AS MaxPromulgateTime, 20 AS WorkingDays, a.PEStandardTypeID, a.StopOrRestart, b.ContractID, 14
b.ProductionMainID, b.ClientID, b.Clientcnm, b.ContractNumber, c.RealAmount, c.SalerAreaId AS AreaID, c.ChildContractNumber, c.ContractDate, 15
c.SalerAreaName AS AreaName, d.IsRenew, d.IsAllUse, d.AcceptAllOrderValidateID, d.AverageQualityModulus, e.PEStandardTypeName16
FROM dbo.WEBT_ProductionChild AS a INNER JOIN17
dbo.ProductionMain AS b ON a.ProductionStateID IN (3, 4, 5, 6, 8, 71, 72, 73, 74, 75, 76, 77) AND a.ProductionMainID = b.ProductionMainID INNER JOIN18
dbo.CSLT_Contract AS c ON b.ContractID = c.ContractID INNER JOIN19
dbo.WEBT_ProductionDetail AS d ON a.ProductionMainID = d.ProductionMainID INNER JOIN20
dbo.WEBD_PEStandardType AS e ON a.PEStandardTypeID = e.PEStandardTypeID21
GO22
CREATE UNIQUE CLUSTERED INDEX V_Index_ProductionChildID ON WHBTest_01(ProductionChildID)23
24
必须用Inner的内连接方式,需要绑定框架
