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

Question

question

DaniSQL asked Mar 4, '10

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,

sql-server-2005 view data-warehouse

1

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ · Jul 14, 2010 at 06:58 AM

Anyone know why this question has two accepted answers? ?!?

0 ·

Answer 1 · 2010-03-04T17:05:01Z

Matt Whitfield answered Mar 4, '10

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...

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-04T17:10:36Z

Kev Riley answered Mar 4, '10

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.

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-03-04T19:12:19Z

TimothyAWiseman answered Mar 4, '10

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.

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2012-06-11T19:54:49Z

Repriser answered Jun 11, '12

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 ,

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2012-12-23T13:54:46Z

betibeau answered Dec 23, '12

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.

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 6 · 2012-12-23T13:57:43Z

betibeau answered Dec 23, '12

around the new table containing the calculated figures, you could deploy a simple delta detection to avoid a complete recreation (when rquired), and then apply update/insert/delete or flag as logically deleted. Cleanup of logically delete during night or week-ends....

10 |1200 characters needed characters left characters exceeded

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question