SQL Data Warehousing – Tip 2 – Indexed Views

  • Article

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 https://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:

The user that executes the CREATE INDEX statement must be the view owner.

The following SET options must be set to ON when the CREATE INDEX statement is executed:

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

The NUMERIC_ROUNDABORT option must be set to OFF. This is the default setting.

If the database is running in 80 compatibility mode or earlier, the ARITHABORT option must be set to ON.

When you create a clustered or nonclustered index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).

The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.

If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.

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:

 

Underlying Tables:

 

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]

GO

 

View Statement:

 

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