Using a View vs.a Temp Table in a Stored Procedure

Hello - Wanted to get some guidance regarding best practices on when to use a View vs a Temp Table in Stored Procedures.

Is there any downfall to using a View for a stored Procedure over using a Temp Table?

The database I am working in is a datawarehouse that is updated once a month. I tend to lean towards using a View because I can always look back on the results in the view if I have questions during that month on how a particlar field was dervied during load.

All that said, I wanted to see id anyone had information on this topic to share? Is a Temp Table better, if so...why? Is it simply a matter of personal preference?

Thanks in advance for yoru time.
Cynthia HillLead ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Cy,

In this case, use a view.  

By definition, a data warehouse is a series of snapshots over time.  If the warehouse is built in any kind of reasonable fashion, it's expected that queries will be over varying time periods.  A view will be perfect here.

By contrast, sometimes events occur that can affect previous periods in a data warehouse.  Accounting is full of things where monies are paid or received in one accounting period for things that happened in a previous period.  If you extract data from a period into a (temp) table and try to keep that table as a snapshot you run the risk of missing those kinds of multi-period updates.

Good Luck!
Jim HornMicrosoft SQL Server Data DudeCommented:
If your view aggregates data it wound be faster to write that as a physical table.  My current employer's data warehouse has all kinds of tables for by month, by quarter, by year, by custom time span, individual client, all clients, you get the idea.

I would say it doesn't matter if this is a data warehouse or any other database. In all cases a view would be the better idea. The reason is that a temp table would be a copy of all the data which is already in the original table(s). You would need to create the same data types, you would need to copy the data and to query the result you would need to create a meaningful index on the table which also uses space in your database.
And all that although you already have the data in the original table(s) and also the index files. So a view can simply use that and return the results only using the index data and that in an optimized way.

If you need the result data of a view to create further from that in a chain then simply use a CTE which is able to work in a cascade like this:
WITH qryOne AS
,qryTwo AS
( SELECT ID, X * 50 AS X2 FROM qryOne WHERE...)
,qryThree AS
(SELECT ID, Y * 100 AS Y2 FROM qryOne WHERE)
SELECT qryTwo.X2, qryThree.Y2 FROM qryTwo INNER JOIN qryThree ON qryTwo.ID = qryThree.ID

Open in new window

This is of course only a silly example but you get the idea - you have only one query as base and use it in multiple further queries until you get the result at the end.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Is there any downfall to using a View for a stored Procedure over using a Temp Table?

There certainly could be, and it's increased I/O.  At times a view is needed, at times a temp table is needed, and other times a permanent table is needed, as Jim stated.

Let's check each case.

Say the main table has only 100 rows of detailed transaction.  Here a view makes perfect sense.  Even though SQL has to re-read all the rows to generate the correct total, that doesn't matter since it's only 100 rows anyway.

Now say the table has 200M rows.  Then, depending on what you're doing, it can make sense to summarize into temp tables, and use those temp tables to compute other totals.

Finally, say the table has 4B rows.  Now permanently storing pre-summarized totals into actual tables make far more sense. Assume new amounts are added every day (only, not more often).  You ultimately need daily, weekly, monthly, quarterly and yearly totals.  You can either pre-summarize and store just the daily totals (meaning at most you have to add 365 rows even to get a yearly total).  Or, for more complex calcs, it can make sense to re-do all the totals, but just once a day, then store them, and re-use anytime someone needs that total.  That total might be queried 5,000 times a day -- there's absolutely no reason to force a view to rematerialize that total every few minutes.
Cynthia HillLead ConsultantAuthor Commented:
Thanks All!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.