[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

#temptable or view

Posted on 2009-12-21
13
Medium Priority
?
289 Views
Last Modified: 2012-05-08
I need to make a staging table for a query. Should I use #temptables or views

Which approach is best
0
Comment
Question by:Mr_Shaw
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 660 total points
ID: 26096855
Views are executed each time you access them. The Query Execution Plan will remain (is not recalculated each time), but the underlaying data might have changed meanwhile, so it is running thru the Query Engine.

Temp tables remain static. If you have a complex query, which is accesses several times (e.g. for subqueries or similar), a temp table is better. And if you are building the content step by step, it is the only means.
0
 

Author Comment

by:Mr_Shaw
ID: 26096879
My data is static. Does that make a difference?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26096909
Hello Mr_Shaw,

How bigis the table, in case thats a big table you can go for an indexed view

Regards,

Aneesh
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 71

Expert Comment

by:Qlemo
ID: 26096917
Static data makes for a better chance to have everything in memory, which will directly influence query speed.

If you use the contents once for the query, I would use the view. If you use some kind of algorithm on it, the temp table might be a better idea.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 300 total points
ID: 26097286
>>Which approach is best>>
As you can see by the responses so far, the answer is, "It depends." The first question is why do you need to make a staging table for a query? It sounds like you're looking for an intermediate place holder for data requiring manipulation before it's entered into a target table. Depending on the complexity of the that manipulation, you might be best off with real tables, temp tables, views, materialized views, or even inline views. Perhaps you can provide a little more detail about what your requirements are so we can get a better feel for the approach that might be taken.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 26097701
Or, "as a last resort", just try yourself. It shouldn't be that different to build either a view or a temporary table.
0
 

Author Comment

by:Mr_Shaw
ID: 26097731
>>Perhaps you can provide a little more detail about what your requirements are so we can get a better feel for the approach that might be taken.

I need a staging table because I am doing a complex query which needs to be broken down in to different stages. It does not work in one query. Long story.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1040 total points
ID: 26099123
With SQL Server 2005/2008, if you are just wanting to break up the logic you can use a common table expression which is a virtual table of the data.  Remember you can also use a table variable. The difference between those and #temptable is that you don't have to create anything on your physical hard drive as in the tempdb.  If this is a persisted staging you have to do (i.e., doing this every time you need this data), then VIEW is your best choice instead of having to recreate a #temptable each time.  I would go with suggestion of INDEXED VIEW as well if you don't have any syntax that forbids this.
0
 

Author Comment

by:Mr_Shaw
ID: 26099459
a view is fine and the idea of an index is great. I am just worried that I will clog up thedatabase with views and the overall query will be scattered between various views.

I suppose i am thinking of the long term continuity of others understanding the query structure...i.e where all the various bits are and where to find them. Maybe I need to use a good naming convention for my views so others will no that the table being access is a view&.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 26099496
Yes.  That is always a good practice anyway.  You will find maintenance easier as this is the same concept behind modular programming.  If one segment of the data needs to change, as long as the resulting columns stay the same you only need to change that portion and not have to worry about everything else.  Just takes a little more discipline where as in one place, we tend to be guilty of changing the values constantly or lazily using "SELECT *".  When dividing the work, it makes explicit select list more important.

Good luck!
0
 

Author Comment

by:Mr_Shaw
ID: 26099977
>>Yes.  That is always a good practice anyway.

which bit are you refering to as good practice?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 26100056
"Maybe I need to use a good naming convention for my views so others will no that the table being access is a view&."

0
 

Author Closing Comment

by:Mr_Shaw
ID: 31668561
thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question