Temp Tables in SQL Server

  • Dinesh Asanka

    SSChampion

    Points: 11058

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/te




    My Blog: http://dineshasanka.spaces.live.com/

  • David.Poole

    SSC Guru

    Points: 75381

    I know that temporary tables are stored in tempdb but where are tables stored in a "table" data type stored?

  • ekoner

    Mr or Mrs. 500

    Points: 551

    David - table variables are stored in memory, if you search in books online under table variables, you will find some help on them. It looks like microsoft would prefer us to use these over temporary tables. See the quote from BOL below:

    Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:

    • A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.

      Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

      INSERT INTO table_variable EXEC stored_procedure

      SELECT select_list INTO table_variable statements.

      table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

    • table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
    • Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.

    Measure twice, cut once

  • rcnelsonmba

    SSC Eights!

    Points: 813

    I have found that table variables have their limitations.  In a datawarehouse environment, with query results totaling hundreds of thousands of rows, or more, table variables are NOT the way to go.  Temp tables work much better.

    But.... thank you very much for the rest of the article.

  • JohnMarks

    Ten Centuries

    Points: 1143

    A common misconception is that table variables are always stored in memory. 

    http://support.microsoft.com/default.aspx?scid=kb;en-us;305977

    Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

    A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

    Generally I find that performance is good with table variables that store smaller amts of data.  A larger dataset usually runs better in a temp table.

     

  • sdoughty

    Say Hey Kid

    Points: 680

  • "Use indexes on temporary tables. Earlier days, I always forget to use a index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables. "
  • Indexing temporary tables is problematic - if you create an index called idxFoo, and 2 users hit the sproc at the same time, then the 2nd user will get an "Index name already exists" type of message.  How can you create a unique index name?

    Steve

  • David.Poole

    SSC Guru

    Points: 75381

    When you create a temporary table its name is always unique.

    You think you have created #Tbl_MyTable and to all intensive purposes you have, but the real name will be #Tbl_MyTable_____...._DE or something similar.

    Index names don't have to be unique to the database, though I would regard duplicate named objects to be bad practice.

    The indices have to be unique to the object on which they are created.

  • ekoner

    Mr or Mrs. 500

    Points: 551

    So would you just use  CREATE INDEX  after creating the table ?

    Measure twice, cut once

  • ThomasH

    SSCertifiable

    Points: 7437

    What is the reason the article states:

  • When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements, create the table using DDL statement and use INSERT INTO to populate the temporary table. 
  • Performance? Readability?

    Thanks

     



    Once you understand the BITs, all the pieces come together

  • David.Poole

    SSC Guru

    Points: 75381

    SELECT INTO is slow, horrendously slow on retrieving large recordsets.

    Creating the table first, then doing and INSERT INTO is much faster, plus you have greater control over the table structure.

    I think SELECT INTO trys to retrieve all data before creating the table and as the data doesn't have anywhere to go until the table is created it simply eats memory.  I could be wrong here.

  • David le Quesne

    SSCertifiable

    Points: 5229

    I use temporary tables to store large results sets for reporting, usually where the values required cannot be calculated in a single SELECT statement. The reporting runs in a third party application with a scripting language which allows me to create and access the temporary tables. Doing this gave my application a huge performance boost (table hints also helped), and helped solved locking problems I was having due to the applications insistance on starting a transaction before running any script code.

    However, I have one process which uses a stored procedure for speed, and have used a shared 'permanent' table because temporary tables go out of scope when the stored procedure finishes, as described in this quote from MS Books Online: 

    "A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table"

    I was wondering if a global temporary table would remain in scope after the procedure had completed, it is not clear from the explanation in Books Online.

    Alternatively, I might be able to return a table datatype from the procedure, and create a local temporary table from that to drive the report.

    Any suggestions welcome!

    If it ain't broke, don't fix it...

  • Jaroslav Urban

    Valued Member

    Points: 71

    Just quick notes about the index for tmp sys table.

    it's useless to create index just after tmp table creation. I add index after I fill table with data. Then the data are reindexed and all queries are faster after that.

    Based on my experience I can say that using of tmp table is slower compare to use share table. Share table is more complicated with management, but faster. In my case, which is about this range (0-4000 rows and 9 columns(mostly varchar or uniqueidetifier)) I also use sys tmp table for related table to my share table, but there are only three columns. And finaly I use also table datatypes, but there is only one column and max 100 records.

    I just don't know how I could solve problem with updating of statistics above this share table due to public role of logged dbuser.

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    This article should have been updated prior to re-publish as there are some errors and misconceptions contained within it. Sorry.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • connel

    Valued Member

    Points: 53

    I agree with the last reply: This article should be rewritten taking into consideration the comments raised by those involved.

    There is no explanantion to justify the statements regarding the limitations of temp tables.

    We use a HUGE number of temp tables in our overnight batch jobs and we are constantly looking where temp tables can be replaced by shared permanent table objects. We also saw the benefit of creating indexes on the temp tables and moved the create index statements after the INSERT to improve performance. Does that apply to the CLUSTERED INDEX too? And should we update statistics following insert of a large number of rows?

    Last question: Is it worth using TABLOCK on the temp table? It should be unique to that connection so in theory not required?

    The article WITH the replies has been very useful. Thanks to all that contributed.

    C

     


    Best Regards,

    C

  • David Burrows

    SSC Guru

    Points: 64737

    Yes you can

    Or I sometimes declare it with the create table, e.g.

    CREATE TABLE #mytable (mykey int PRIMARY KEY CLUSTERED, mycol char(1))

    to let SQL make up the name

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Viewing 15 posts - 1 through 15 (of 46 total)

    You must be logged in to reply to this topic. Login to reply