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)
Best Answer
Indexed views often can't be used as drop-in replacements for tables. The reason is that the
WITH (NOEXPAND)
hint is often needed. Often, the view is expanded (despite the index) and the underlying definition is used. Paul White has written about it previously.Change queries from:
SELECT * FROM SkaData.FiscalPeriod
To:
SELECT * FROM SkaData.FiscalPeriod WITH(NOEXPAND)