locked
Choosing between temporary tables and table variables. RRS feed

  • Question

  • I want to keep a set of records in multiple temporary storage for reporting purpose. This set of records is filtered depending upon user criteria. Each time user selects a menu option, records in temporary tables are wiped out and a new set of records is filled in these tables.

    The problem is that I have not taken concurrency issue in mind and I am sure wiping out the table records is going to create problems when two or more users access the menu at the same time.

    I want a better way to create temporary storage for reports for each application session. This temporary storage should automatically be wiped out once the session expires.

    I want to know whether to create Temporary Tables with SessionID column or make use of Temporary Variables.

    If the latter is chosen, is it possible to view the resultset via Management Studio?

    Monday, November 28, 2011 5:27 PM

Answers

  • Perhaps the simplest solution, without much change, would be to ADD a user COLUMN to the TABLE. Then only wipe per user.
    • Marked as answer by OldEnthusiast Sunday, December 4, 2011 8:14 AM
    Monday, November 28, 2011 5:34 PM
    Answerer
  • Hi Old !

    Why worrying about concurrency. Have you remember this Local Temporary Tables are only available only for that particular session.

    Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server

     

    <a href="http://msdn.microsoft.com/en-us/library/ms186986.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms186986.aspx</a>
    

     

    Note : Create Local temporary table as described earlier and it will be dropped automatically once you disconnect from instance.


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    • Marked as answer by OldEnthusiast Sunday, December 4, 2011 8:14 AM
    Monday, November 28, 2011 8:36 PM
    Answerer
  • I want to know whether to create Temporary Tables with SessionID column or make use of Temporary Variables.


    #temptables and @tablevariables are setup for multi-user support: they are only visible in the current session.

    If you need visibility outside the (creating) session, you need permanent table with CookieID for example.  An SQL Agent job can be setup for periodic purging of expired info.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by OldEnthusiast Sunday, December 4, 2011 8:14 AM
    Tuesday, November 29, 2011 10:42 AM

All replies

  • Perhaps the simplest solution, without much change, would be to ADD a user COLUMN to the TABLE. Then only wipe per user.
    • Marked as answer by OldEnthusiast Sunday, December 4, 2011 8:14 AM
    Monday, November 28, 2011 5:34 PM
    Answerer
  • Hi Old !

    Why worrying about concurrency. Have you remember this Local Temporary Tables are only available only for that particular session.

    Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server

     

    <a href="http://msdn.microsoft.com/en-us/library/ms186986.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms186986.aspx</a>
    

     

    Note : Create Local temporary table as described earlier and it will be dropped automatically once you disconnect from instance.


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    • Marked as answer by OldEnthusiast Sunday, December 4, 2011 8:14 AM
    Monday, November 28, 2011 8:36 PM
    Answerer
  • I want to know whether to create Temporary Tables with SessionID column or make use of Temporary Variables.


    #temptables and @tablevariables are setup for multi-user support: they are only visible in the current session.

    If you need visibility outside the (creating) session, you need permanent table with CookieID for example.  An SQL Agent job can be setup for periodic purging of expired info.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by OldEnthusiast Sunday, December 4, 2011 8:14 AM
    Tuesday, November 29, 2011 10:42 AM