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

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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