我需要通过包含新创建的表中的附加列值来更新现有的 MS SQL 索引视图。
索引视图:
CREATE OR ALTER VIEW [dbo].[MySelectionInfo]
WITH schemabinding
AS
SELECT C.Id id0,
C.Code Code1,
C.Name Name2,
C.ProgramLevel Level3,
C.Department Department4,
C.City City10,
C.STATE State11,
C.StartDate StartDate12,
C.Deadline Deadline13,
B.ID Table_B_ID,
A.Id Table_A_ID
FROM dbo.Table_A A
INNER JOIN dbo.Table_B B ON A.id = B.Table_A_Id
INNER JOIN dbo.Table_C C ON C.Table_B_Id = B.Id
新表:
CREATE TABLE [dbo].[Table_D] (
[Id] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
[ModelName] [varchar](max) NOT NULL,
[Table_C_Id] [int] NOT NULL,
[AttributeValue] [varchar](max) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
CONSTRAINT FK_Table_C_Id FOREIGN KEY (Table_C_Id) REFERENCES some_schema.dbo.[Table_C] (Id)
ON DELETE CASCADE
ON UPDATE CASCADE
)
Data in the new table:
我只想包含一些 ModelName 列值作为列名称,并将 AttributeValue 作为值包含在select * from [dbo].[MySelectionInfo]
结果集:
我可以使用以下方法达到预期的结果PIVOT
功能:
CREATE OR ALTER VIEW [dbo].[MySelectionInfo]
WITH schemabinding
AS
SELECT C.Id id0,
C.Code Code1,
C.Name Name2,
C.StartDate StartDate12,
C.Deadline Deadline13,
B.ID Table_B_ID,
A.Id Table_A_ID
FROM dbo.Table_A A
INNER JOIN dbo.Table_B B ON A.id = B.Table_A_Id
INNER JOIN dbo.Table_C C ON C.Table_B_Id = B.Id
LEFT JOIN (SELECT PivotTable.Table_C_Id,
PivotTable.attribute1,
PivotTable.attribute2,
PivotTable.attribute3
FROM (SELECT Table_D.Table_C_Id,
Table_D.ModelName,
Table_D.AttributeValue
FROM dbo.Table_C
INNER JOIN dbo.Table_D
ON Table_C.Id = Table_D.Table_C_Id) AS sourceTable
PIVOT (
Max(AttributeValue) FOR ModelName IN (attribute1, attribute2, attribute3)
) AS PivotTable) dbo.Table_D D ON D.Table_C_Id = C.Id
But,运行上面的SQL语句后,我无法为视图创建聚集索引,因为LEFT
JOIN
, PIVOT
, MAX
禁止用于索引视图 https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15.
问题:是否有任何其他解决方案可以实现所需的结果,并且仍然将现有视图作为索引视图?