Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Temp Tables in SQL Server Expand / Collapse
Author
Message
Posted Saturday, February 7, 2004 5:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:38 PM
Points: 912, Visits: 209
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/
Post #99216
Posted Wednesday, February 11, 2004 1:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 2,902, Visits: 1,818
I know that temporary tables are stored in tempdb but where are tables stored in a "table" data type stored?

LinkedIn Profile
Newbie on www.simple-talk.com
Post #99726
Posted Wednesday, February 11, 2004 4:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 37, Visits: 655

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





Post #99758
Posted Wednesday, February 11, 2004 5:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 3, 2007 5:25 AM
Points: 73, Visits: 1

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.




Post #99780
Posted Wednesday, February 11, 2004 9:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 64, Visits: 484

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.

 




Post #99856
Posted Wednesday, February 11, 2004 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 5, 2010 6:34 PM
Points: 32, Visits: 54
  • "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




    Post #99906
    Posted Thursday, February 12, 2004 1:42 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Today @ 3:49 AM
    Points: 2,902, Visits: 1,818

    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.



    LinkedIn Profile
    Newbie on www.simple-talk.com
    Post #99967
    Posted Thursday, February 12, 2004 1:53 AM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: 2 days ago @ 7:48 AM
    Points: 37, Visits: 655
    So would you just use  CREATE INDEX  after creating the table ?

    Measure twice, cut once





    Post #99970
    Posted Monday, February 16, 2004 3:21 PM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Tuesday, March 29, 2005 2:31 PM
    Points: 393, Visits: 1
    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
    Post #100624
    Posted Wednesday, February 18, 2004 1:36 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Today @ 3:49 AM
    Points: 2,902, Visits: 1,818

    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.



    LinkedIn Profile
    Newbie on www.simple-talk.com
    Post #100886
    « Prev Topic | Next Topic »

    Add to briefcase 12345»»»

    Permissions Expand / Collapse