none
view Vs temp table

    Question

  • can anyone please explain me the differnce of having a view replaced with a temp table.
    the reason i am asking is one of our jobs takes 4 hours to complete using a view, if the same view is replaced by a table, it just takes 20 minutes. If the base tables are being modified, will the results be the same for both , ie using a view vs a temp table in place of a view..pls help..thnks in advnace.
    Niki
    Tuesday, May 12, 2009 7:54 PM

Answers

  • Each time you query a view, it executes the underlying select statement and retrieves the data from the base tables. (Indexed Views operate slightly differently, but their relevance seems to be outside the scope of your question).

    A temp table is a completely separate database object. It gets populated with rows when you insert them into it. It has no ongoing relationship to any query that may have been used to insert data into the temp table.

    So, when you query a view, the results will show data from the underlying base table even if they've been updated recently. When you query a temp table, the results will show data as it was put into the temp table; if the table originally used to populate the temp table changes, you will not see those changes in the temp table.

    HTH...

    Joe


    Joe Webb, SQL Server MVP | http://weblogs.sqlteam.com/joew/
    Wednesday, May 13, 2009 12:04 PM
    Moderator

  • P.S.  If the view is indexed (materialized) all bets are off!
    This is not true, and SQL Server does not work in the manner that other products like Oracle would in this aspect.  Indexed Views are updated as the data changes in the tables as a part of the transaction changing the information.  This is why there are limitations and criteria that govern what kinds of views can be indexed or not (ie, no self joins).


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by neophytenik Thursday, May 14, 2009 1:09 AM
    Wednesday, May 13, 2009 2:24 PM
    Moderator

All replies

  • Temp tables and views are totally different from each other. What to use that will depend on what you are doing.  Typcially views are not stored on disk, and the data have to be queried from the disk. Temp tables take the data that you need into a table in the tempdb database for processing.

    This help??
    Sandip
    SP
    Tuesday, May 12, 2009 8:23 PM
  • no, rather my question was if a use a  temp table(or any table for that matter with the same definition as that of a view) instead of a view definition , will that make any difference to the output if the base tables are modified.

    for example, here is the query, i have a view that says,
    create view as
    select a from table A
    inner join b from Table B

    and i have a table creted

    create table
    select a from table A
    inner join b from Table B

    i use a view inside a code and use a table (instead of view) inside another code, if the base tables that is A and B are modified, any change in the output between these two codes.
    Tuesday, May 12, 2009 8:56 PM
  • It depends how you are processing the view now.  If you are querying it multiple times, the data will reflect the underlying data at the time of the query. 

    When you build the temp table, it will reflect the source tables at the time of the build - it will not reflect any changes - it is static.
    Tuesday, May 12, 2009 9:15 PM
  • Each time you query a view, it executes the underlying select statement and retrieves the data from the base tables. (Indexed Views operate slightly differently, but their relevance seems to be outside the scope of your question).

    A temp table is a completely separate database object. It gets populated with rows when you insert them into it. It has no ongoing relationship to any query that may have been used to insert data into the temp table.

    So, when you query a view, the results will show data from the underlying base table even if they've been updated recently. When you query a temp table, the results will show data as it was put into the temp table; if the table originally used to populate the temp table changes, you will not see those changes in the temp table.

    HTH...

    Joe


    Joe Webb, SQL Server MVP | http://weblogs.sqlteam.com/joew/
    Wednesday, May 13, 2009 12:04 PM
    Moderator
  • Noviceniks,

    To answer your question specifically - the view will reflect changes whereas the fixed table will not.

    Paul

    P.S.  If the view is indexed (materialized) all bets are off!
    Wednesday, May 13, 2009 12:13 PM
  • Thanks a lot for the reponse
    Wednesday, May 13, 2009 2:18 PM

  • P.S.  If the view is indexed (materialized) all bets are off!
    This is not true, and SQL Server does not work in the manner that other products like Oracle would in this aspect.  Indexed Views are updated as the data changes in the tables as a part of the transaction changing the information.  This is why there are limitations and criteria that govern what kinds of views can be indexed or not (ie, no self joins).


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by neophytenik Thursday, May 14, 2009 1:09 AM
    Wednesday, May 13, 2009 2:24 PM
    Moderator