Last time we discussed the use of a period table to consolidate analysis into smaller segments of data.  This can be done for an analytical application where the details are not needed or as part of a rollup.  Today, we’re going to look at how to use the period table.  For the application discussed in part 1, it would be useful to have information related to analyzing the period quickly accessible without having to aggregate all of the history rows.  To do this, we create an indexed view with schema binding.

There are several rules for indexed view discussed in the “Creating Indexed Views” article at http://msdn.microsoft.com/en-us/library/ms191432.aspx.  This includes not using AVG, MIN, MAX and using COUNT_BIG with SUM in order to derive the averages as well as restrictions requiring the use of ANSI_NULLS, QUOTED_IDENTIFIER as summarized below: