|
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,
(comments are locked)
|
|
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...
(comments are locked)
|
|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
|
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 ,
(comments are locked)
|
|
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.
(comments are locked)
|
1 2 next page »
Anyone know why this question has two accepted answers? ?!?