Things You Didn't Know About Temp Tables and Table Variables

  • Roi Assa

    SSChasing Mays

    Points: 611

    Comments posted to this topic are about the item Things You Didn't Know About Temp Tables and Table Variables


    Kindest Regards,

    Roi Assa

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    Another thing never said: when creating temp table is better adding the "collate" to char/varchar/text etc. cause of different collate in tempdb e the current db.:w00t:

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Hello Roi,

    you actually can add constraints to table variables.

    Example:

    DECLARE @MyTabVar TABLE(

    PKCol int NOT NULL PRIMARY KEY CLUSTERED

    ,UniqueCol int NOT NULL UNIQUE

    )

    Best Regards,

    Chris Büttner

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Nice work! Your article was very informative!

  • sbateman-803743

    SSC Veteran

    Points: 222

    remember you can use tempdb as a nearly normal database**

    I create Structure for XML explict table and error_log table in tempdb and JOB-Agent bcp's these to files.xml

    Their STRUCTURES/Programmes are copied into model

    Every time SQL-2005 is restarted empty tables are created in tempdb,from model, and are then populated as required.

    this data is available if error is found and correction to 'Standard' database table can be made - otherwise I am not sad to loose-data on sql_shutdown! We have to many tables in standard named as xtemp****** but nobody remembers what they were for!

    **An article on differences between Tempdb/Model and 'STANDARD' sql-tables is probably some-where, it is just that I have been doing it this way for ages!

  • blandry

    SSCarpal Tunnel

    Points: 4821

    Great article, and thanks for it - but under the heading of "the more I learn the less I know"...

    Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?

    I ask this because we have an app that generates reports by calling stored procedures. So we are only grabbing data when a report is being prepped to run. But as we design stored procs and sometimes play with Temp Tables versus Unions, or Temp Tables versus Table variables, we have yet to see any major significant difference in performance and I am left wondering if these are all just pretty much the same?

    Thanks again for a great post!

    There's no such thing as dumb questions, only poorly thought-out answers...
  • WayneS

    SSC Guru

    Points: 95387

    Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?

    One thing that you CAN do with a #temp table that you can NOT do with a @Table variable is to create indexes on columns. As another poster noted, you can create the PK (and thus the PK index) on a @Table variable - but only if it is included in the declare statement. Whereas with a #temp table, you can follow up the create table (or select into) statement with create index statements. This can give the #temp table a big performance benefit, especially if you are loading it with a lot of data.

    I agree that this is an excellent article... especially the part about the named constraints.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • BobAtDBS

    SSCarpal Tunnel

    Points: 4584

    I'll be the one to ask the idiot question:

    When is it advantageous to use a table variable? Since I started in Version 7, when they didn't exist, I just have never bothered to use one yet. When I need a temp table, I've never felt constrained by it - you can call another SP and let it reference your temp table which is what I thought was the big deal with table variables. You can't pass a table variable into a SP from ADO (now THAT would be cool).


    Student of SQL and Golf, Master of Neither

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    sbateman (7/17/2008)


    remember you can use tempdb as a nearly normal database**

    I create Structure for XML explict table and error_log table in tempdb and JOB-Agent bcp's these to files.xml

    Their STRUCTURES/Programmes are copied into model

    Every time SQL-2005 is restarted empty tables are created in tempdb,from model, and are then populated as required.

    this data is available if error is found and correction to 'Standard' database table can be made - otherwise I am not sad to loose-data on sql_shutdown! We have to many tables in standard named as xtemp****** but nobody remembers what they were for!

    **An article on differences between Tempdb/Model and 'STANDARD' sql-tables is probably some-where, it is just that I have been doing it this way for ages!

    Hmmm that makes sense... Grab the data out of tempdb...

  • sbateman-803743

    SSC Veteran

    Points: 222

    on area where I have been back to #table is after CTE

    eg

    ;WITH cte_OldTraders (TRID, LastSOdate)

    AS (

    SELECT traderid, MAX(orderdate)

    FROM salesorders

    WHERE status != N'CANCELLED'

    GROUP BY traderid

    )

    --

    SELECT customers.id AS xtrid

    , isNull(LastSOdate,customers.createddate) AS xsdat

    INTO #soldtraders

    FROM customers

    LEFT OUTER JOIN cte_OldTraders ON (customers.id = TRID)

    WHERE [tradingstatus] = N'CURRENT';

    --

    BEGIN TRAN

    UPDATE traders

    SET [tradingstatus] = N'DORMANT', [isonhold] = 0

    FROM traders

    INNER JOIN #soldtraders ON (id = xtrid)

    WHERE [dbo].[xfn_julian_date](xsdat) < 3400 -- aging Function

    COMMIT TRAN

    I could not get @soldtraders working

    so it is GOOD to have knowledge of both

  • cy-dba

    SSCarpal Tunnel

    Points: 4149

    Good article! Although I have not used table variables much in the past, I have started using them more and will keep in mind data integrity issues going forward.

  • Charles Kincaid

    SSChampion

    Points: 13593

    Great article. Short, to the point, and well written.

    ATBCharles Kincaid

  • JJ B

    SSCertifiable

    Points: 5042

    I'd like to second BobAtDBS's question.

    For some reason, I find the syntax for table variables to be more attractive. I started using them as soon as they were available to me. Since I rarely use table variables/temp tables and they have so far only been to hold relatively small amounts of data (with no need for constraints, etc.), it doesn't seem to matter which one I use.

    While there may be a time where it doesn't matter which one you use, is there ever a time where table variables convey an advantage over temp tables? Why did MS add table variables if there aren't at least some circumstances where there is an advantage?

    Here's my *guess*: There is a new feature coming out in SQL Server 2008 where you can pass table variables as arguments/parameters to stored procs. Perhaps this only works for table variables and not temp tables?

    Even if that's true, it doens't explain why they created table variables for SQL Server 2005 (or was it earlier).

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    blandry (7/17/2008)


    Great article, and thanks for it - but under the heading of "the more I learn the less I know"...

    Is there really any difference or advantage between Temp Tables and Table variables? If there is not enough cache on hand and both will eventually hit TempDB - is the only advantage that Table variables dont hit it explicitly to start?

    I agree with Blandry, this was a great article.

    As to advantages and disadvantages, as many people have said sometimes one is better than the other. The ability to create indexes, add columns and use other "Alter Table" commands on a table variable can be handy in certain occasions.

    I personally prefer to use table variables in most situations[/i] because they only exist in the context of the currently running script whereas temp tables exist within your session. This can make a big difference about whether or not I need to worry about dropping temp tables and checking for the existence of temp tables if I am debugging and script and therefore running it over and over with small changes.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • JJ B

    SSCertifiable

    Points: 5042

    I personally prefer to use table variables in most situations because...

    Oh ya! I remember now. Thanks!!

Viewing 15 posts - 1 through 15 (of 42 total)

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