Using SQL Server indexed views for time averaged aggregations

I found many website games such as quiz systems have a need to report “most popular in last 24 hours” or other such time aggregations. For these high ratios of reads / writes it is usually a good trade off to create an indexed view to maintain some aggregations over time. Sometimes the time slice can be midnight-based 24 hour periods, in other circumstances you need greater granularity.

Here is a simple example of maintaining an aggregated view that is addressable by a given day a minute.

create table timeSeries (
timeSeriesId int identity primary key clustered
,updateDate datetime not null
,payload float not null
)

insert timeSeries values (‘2009-06-16 12:00:00’, rand())
insert timeSeries values (‘2009-06-16 12:00:59’, rand())
insert timeSeries values (‘2009-06-16 12:01:00’, rand())
insert timeSeries values (‘2009-06-16 12:59:00’, rand())
insert timeSeries values (‘2009-06-16 01:00:00’, rand())
insert timeSeries values (‘2009-06-16 1:30:00’, rand())
insert timeSeries values (‘2009-06-16 23:59:00’, rand())
insert timeSeries values (‘2009-06-17 00:01:00’, rand())
insert timeSeries values (‘2009-06-17 00:01:30’, rand())

create view timeSeriesByMinute_IV with schemabinding as
select
dayBucket = datediff(day, 0, updateDate)
,minuteBucket = datediff(minute, 0, (updateDate – datediff(day, 0, updateDate)))
,payloadSum = sum(payLoad)
,numRows = count_big(*)
from dbo.timeSeries
group by
datediff(day, 0, updateDate)
,datediff(minute, 0, (updateDate – datediff(day, 0, updateDate)))
go

create unique clustered index CU_timeSeriesByMinute_IV on timeSeriesByMinute_IV (dayBucket, minuteBucket)
go

create view timeSeriesByMinute as
select
dayBucket
,minuteBucket
,payloadSum
,numRows
,payloadAvg = payloadSum / numRows
from dbo.timeSeriesByMinute_IV with (noexpand)
go

declare @timeLookup datetime, @dayBucket int, @minuteBucket int
select
@timeLookup = ‘2009-06-16 12:00:00’
,@dayBucket = datediff(day, 0, @timeLookup)
,@minuteBucket = datediff(minute, 0, (@timeLookup – datediff(day, 0, @timeLookup)))

select * from timeSeriesByMinute where dayBucket = @dayBucket and minuteBucket = @minuteBucket

If you have the Enterprise sku of SQL Server then the NOEXPAND hint is not required to force an indexed view to make use of its index. Unfortunately the Standard sku will not use the index by default and the NOEXPAND hint is required. To help other developers to never miss this required hint I always implement indexed views with the above base-view / wrapper-view approach. The wrapper view is the only one that folks reference so  it guarantees the NOEXPAND hint is applied.