x

Is there an easy equivalent to Materialized view in SQL Server?

Hi All,

I am production DBA and today i was asked by one of the developers if I can come up with an equivalent way of materialized view in SQL Server. I didnt know much about materialized views in the first place and I asked why, I was told they need a way to store/cache calculated aggregate values in views. They dont want to run expensive query to the table directly. I suggest that we build a data warehouse so that users can directly query the info they want from the DW. But they dont like the idea since the data in the base table itself is loaded every time from many places. I am looking at indexed views right now but they seem complicated and hard to maintain. Also aggregate values are going to be stored in the views.

I guess my question is how do you do it easily if you are assigned with the same task?

FYI: My enviroment is SQL 2005 on windows 2003.

Thank you,

more ▼

asked Mar 04 '10 at 04:23 PM in Default

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

Anyone know why this question has two accepted answers? ?!?
Jul 14 '10 at 06:58 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Indexed views are the easy way. The complicated bit about them is getting them to work in the first place by fulfilling the requirements (i.e. fully deterministic, schema-bound data). But once you've done that, indexing is no harder than indexing any table, and the maintenance is just the same...

more ▼

answered Mar 04 '10 at 05:05 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Thanks. Can I store aggregate values inside indexed views?
Mar 04 '10 at 05:23 PM DaniSQL
You can store whatever you can return from a view - so yes...
Mar 04 '10 at 06:11 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

The rough equivalent is indexed views, as you have already discovered.

The other alternative, like you have already mentioned, is to build this data into a real table (akin to datawarehousing), but this only really works if the changes to the base data can be timed, with a fairly fixed frequency - trying to do this with constant or erratically changing data is impossible.

more ▼

answered Mar 04 '10 at 05:10 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Thanks. Any other way to calculate and store/cache aggregate values instead of calculating them on the fly?
Mar 04 '10 at 05:27 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left

Matt and Kev are both completely right, but one thing to remember is that if you are not using Enterprise edition you may need to use the NoExpand query hint to take full advantage of the view. More detail on this is available at http://www.sqlservercentral.com/articles/Indexed+Views/63963/ which is something I wrote a while ago when we started using indexed views.

Depending on your exact needs, you may also be able to add calculated columns to your table and put indexes on the calculated column.

more ▼

answered Mar 04 '10 at 07:12 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left

On the base table, you can create "computed columns" ("PERSISTED"). With that you can create views and/or indexed views using those columns.

Jason http://dbace.us ,
more ▼

answered Jun 11 '12 at 07:54 PM

Repriser gravatar image

Repriser
0

(comments are locked)
10|1200 characters needed characters left
depending on the velocity of insert or update on the base table (the one to be aggregate or calculation made with) just create a specific table for reporting. And, based on the frequency of change decide to recreate on a daily basis or 3 x times a day or what ever.
more ▼

answered Dec 23 '12 at 01:54 PM

betibeau gravatar image

betibeau
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x61
x28

asked: Mar 04 '10 at 04:23 PM

Seen: 14529 times

Last Updated: Dec 23 '12 at 01:57 PM