Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in need of getting data from multiple select queries. So I thought of creating two views and to inner join them to get my data.

My questions is: which option is better

  1. creating a view,
  2. creating temporary table
share|improve this question
3  
If a join satisfies the query you want to run, why do you need a view or a temporary table? How do you define "better"? –  Aaron Bertrand Jun 18 '12 at 12:55
    
@Aaron..the problem is when i use a single join query i won't get the result because the conditions are different for the select query. so i have to select the data in two ways... –  Sivajith Jun 18 '12 at 16:09
    
Then please provide more details. How are you going to "inner join the two joins" as you stated in your question? Can you show some sample data and desired results, so we have some clue what you're talking about? –  Aaron Bertrand Jun 18 '12 at 16:14

1 Answer 1

Using two VIEWs in your SELECT statement will always be better because it can combine both of the views query plans into a single optimised query. Very neat solution which is also fast.

BTW. Using an in-memory temp table > 100 rows generally poor performance because the query plan optimiser assumes there is only 1 row in the temp table.

share|improve this answer
1  
When you say "in-memory temp table" are you referring to a table variable? –  JNK Jun 18 '12 at 15:37
    
@Simon Hughes..thank you Simon..its a great information to me..(by temp table i mean #tables. i think u also mean it). –  Sivajith Jun 18 '12 at 16:05
    
i also want to know what you mean by 'in-memory temp table'?. –  Sivajith Jun 18 '12 at 16:11
1  
I don't know if I would agree that joining two views will be faster than a single join between the same tables. Can you show an example? –  Aaron Bertrand Jun 18 '12 at 16:13
    
There are table variables and there are temp tables. Comparing Table Variables with Temporary Tables. Both may be stored in memory or on disk. –  Mikael Eriksson Jun 18 '12 at 16:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.