I have this table
create table SkaData.FiscalPeriod( PeriodNo int not null constraint FiscalPeriod_FK_BasePeriod references SkaData.Period(Number) ,YearEndMonthNo int not null constraint Fiscalperiod_CK_YearEnd check (YearEndMonthNo between 1 and 12) ,ContributionType char(4) not null constraint FiscalPeriod_CK_ContribType check(ContributionType in ('Base','Cr','Dr')) ,ContributionSign int not null constraint FiscalPeriod_CK_ContribSign check(ContributionSign in (+1,-1)) ,ContributionPeriodNo int not null constraint FiscalPeriod_FK_ContribPeriod references SkaData.Period(Number) ,constraint FiscalPeriod_PK unique clustered (PeriodNo,YearEndMonthNo,ContributionType) );
which is currently populated as
insert Skadata.FiscalPeriod( PeriodNo,YearEndMonthNo,ContributionType,ContributionperiodNo,ContributionSign) select data.PeriodNo ,YearEndMonthNo ,ContributionType ,ContributionPeriodNo = pvt.PeriodNo ,ContributionSign = pvt.Sign from ( select YearEndMonthNo = N ,PeriodNo = Period.Number ,CreditPeriodNo = Period.Number - MonthNo ,DebitPeriodNo = Period.Number - MonthNo + N - 12 from SkaData.Period join (select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) )Number(N) )Number(N) on Number.N >= MonthNo + 12 - Period.Number ) data cross apply (values (PeriodNo, +1, 0, 'Base') ,(CreditPeriodNo, +1, 12, 'Cr') ,(DebitPeriodNo, -1, 12, 'Dr') ) pvt(PeriodNo, Sign, TestPeriod,ContributionType) where YearEndMonthNo <> pvt.TestPeriod
which allows the easy conversion from Calendar YTD values to non-Calendar YTD values.
However the table is a derived table, meaning that it must be maintained as new years are instantiated. I would like to replace it with an Indexed View to eliminate the need for this maintenance.
I have proved the equivalence of the select above with that below, using a NUMBERS table, which is precise and deterministic and uses no sub-queries or APPLY operators, allowing it to be the definition of an Indexed View:
create view SkaData.FiscalPeriod with schemabinding as select PeriodNo = cast(Period.Number as int) ,YearEndMonthNo = cast(months.N/3 + 1 as int) ,ContributionType = cast(case months.N%3 when 0 then 'Base' when 1 then 'Cr' when 2 then 'Dr' end as char(4)) ,ContributionPeriodNo = cast(case months.N%3 when 0 then Period.Number when 1 then Period.Number - MonthNo when 2 then Period.Number - MonthNo + (months.N/3) + 1 - 12 end as int) ,ContributionSign = cast(case months.N%3 when 0 then +1 when 1 then +1 when 2 then -1 end as int) from SkaData.Period join SkaData.Number months on (months.N/3) + 1 >= MonthNo + 12 - Period.Number and months.N between 0 and 33 go create unique clustered index FiscalPeriod_PK on SkaData.FiscalPeriod(PeriodNo,YearEndMonthNo,ContributionType); go
Note that the single clustered index on the view is identical to the (one and only) clustered index on the original table.
However, several queries running against the Indexed View run slower (averaging about 3*, ranging up to about 6*, slower) than against the original table.
Does anyone know why this could happen? Is it a possible bug in the Engine to not treat two identical clustered indices identically? My test data currently covers only two periods, one year apart.
I initially thought it might be due to the columns of the view being nullable, but using isnull to coalesce them simply makes the queries so slow I can't even measure the performance.
I am on SQL Server 2014:
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (Intel X86) Jul 3 2017 02:37:05 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X64> (Build 7601: ) (WOW64)