MS SQL:通过包括新表中的其他列来更改索引视图 [英] MS SQL: alter indexed view by including additional columns from new table

问题描述

我需要通过包括新创建的表中的其他列值来更新现有的MS SQL索引视图. 索引视图:

I need to update existing MS SQL indexed view by including additional columns values from newly created table. Indexed view:

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
)

新表中的数据:

我只想将某些ModelName列值包括为select * from [dbo].[MySelectionInfo]结果集中的值作为列名和AttributeValue作为值:

I want to include only some of the ModelName column values as a column names and AttributeValue as values in the select * from [dbo].[MySelectionInfo] result set:

我可以使用PIVOT函数获得所需的结果:

I can achieve the desired result using the PIVOT function:

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

但是，在运行上述SQL语句之后，我无法为视图创建聚集索引，因为禁止了LEFT JOIN，PIVOT，MAX用于

But, after running the SQL statement above, I am not be able to create the clustered index for the view, because LEFT JOIN, PIVOT, MAX are prohibited to be used in the indexed views.

问题:是否还有其他解决方案可以实现所需的结果，并且仍将现有视图作为索引视图?

Question: Is there any other solutions to achieve the desired result and still have an existing view as an Indexed view?

推荐答案

如果您的数据允许，则可以使用case语句而不是PIVOTing进行制表符

if your data allow it, you could cross tab with case statements instead of PIVOTing:

/*
drop view dbo.mytestinfoview;
go
drop table dbo.Table1;
go
--*/

create table dbo.Table1
(
    id int,
    ModelName varchar(20),
    AttributeValue int
);


insert into dbo.Table1(id, ModelName, AttributeValue)
select distinct o.object_id, concat('attribute', v.id), o.attributevalue
from 
(
    select 
        object_id , 
        abs(checksum(newid())) % 2 as attributevalue
    from sys.objects
) as o
cross apply (values(1), (2), (3), (4)) as v(id)
go

create or alter view dbo.mytestinfoview
with schemabinding
as
    select id, count_big(*) as thecounter,
        sum(isnull(case ModelName when 'attribute1' then AttributeValue end, 0)) as attribute1,
        sum(isnull(case ModelName when 'attribute2' then AttributeValue else 0 end, 0)) as attribute2,
        sum(isnull(case ModelName when 'attribute3' then AttributeValue else 0 end, 0)) as attribute3
    from dbo.Table1
    group by id
go

create unique clustered index idx_v1 on dbo.mytestinfoview(id);
go

select * from dbo.mytestinfoview;
go

这篇关于MS SQL:通过包括新表中的其他列来更改索引视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！