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:
So, given the below table and our period table from last time, we create an indexed view as follows. Notice the conversion of the dailyLow and DailyHigh fields to Money (they were stored as decimal in the equity history table). You can’t include any imprecise data in the aggregations, hence the need for the convert. Make sure your query options are correct before executing the indexed view statement:
CREATE TABLE [dbo].[EquityHistory]( [TradingSymbol] [varchar](25) NOT NULL, [MarketDate] [date] NOT NULL, [PriceAtClose] [float] NULL, [Volume] [bigint] NOT NULL, [DailyHigh] [float] NOT NULL, [DailyLow] [float] NOT NULL, [PriceAtOpen] [float] NULL, [DateUpdated] [datetime2](0) NULL, CONSTRAINT [PK_EquityHistory] PRIMARY KEY CLUSTERED ( [TradingSymbol] ASC, [MarketDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
CREATE VIEW [dbo].[IndexView_EquityHistoryPeriod] with SCHEMABINDING AS
SELECT TradingSymbol, PeriodId, StartDate, EndDate, MonthsInPeriod, SUM(CONVERT(MONEY, DailyLow, 0)) AS SumPeriodLow, SUM(CONVERT(MONEY, DailyHigh, 0)) AS SumPeriodHigh, SUM(Volume) AS TotalVolume, COUNT_BIG(*) AS TradingDayCount FROM dbo.EquityHistory INNER JOIN dbo.Period ON MarketDate BETWEEN StartDate AND EndDate WHERE (Volume > 0) GROUP BY TradingSymbol, PeriodId, StartDate, EndDate, MonthsInPeriod
Below is the SQL to build the clustered index.
One thing to watch out for, when altering a view like this, the index is not scripted by default – i.e, if you do modify view or script view as alter, the index will not show up and when you recreate the view, the index is removed.
SET ARITHABORT ON GO
SET CONCAT_NULL_YIELDS_NULL ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_NULLS ON GO
SET ANSI_PADDING ON GO
SET ANSI_WARNINGS ON GO
SET NUMERIC_ROUNDABORT OFF GO
CREATE UNIQUE CLUSTERED INDEX [IX_IndexedView_EquityHistoryPeriod] ON [dbo].[IndexView_EquityHistoryPeriod] ( [PeriodId] ASC, [TradingSymbol] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO