SQL Data Warehousing – Tip 2 – Indexed Views

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:

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s