Table Variable

  • Hugo Kornelis (8/16/2012)


    sestell1 (8/16/2012)


    A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.

    Actually, this is not a drawback and you don't have to work around it. In the link that was included in the answer of this question (which leadsd to a change request posted by a user, and closed by Microsoft), a Microsoft engineer posts the following information:

    "There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb."

    You do if you are re-using it for temporary storage for a loop, and are using the identity as your loop sequence. Delete does not reset the identity column. You'd need to either set your loop to get the min/max values, or set your own sequence values without using an identity.

  • Hugo Kornelis (8/16/2012)


    sestell1 (8/16/2012)


    A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.

    Actually, this is not a drawback and you don't have to work around it. In the link that was included in the answer of this question (which leadsd to a change request posted by a user, and closed by Microsoft), a Microsoft engineer posts the following information:

    "There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb."

    But you don't get the IDENTITY reseed that Truncate gives.

    DECLARE @bob-2 TABLE(

    tid INT IDENTITY(1,1) PRIMARY KEY,

    tval VARCHAR(50))

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    SELECT * FROM @bob-2

    DELETE @bob-2

    INSERT @bob-2 DEFAULT VALUES

    SELECT * FROM @bob-2

    That said, I found reading the comments kind of amusing.

    "We're using table variables because of the performance increase from temp tables"

    Ummm



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Neat trick mtassin, I've never seen an insert statement using DEFAULT and no columns listed before.

  • mtassin (8/16/2012)


    Hugo Kornelis (8/16/2012)


    sestell1 (8/16/2012)


    A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.

    Actually, this is not a drawback and you don't have to work around it. In the link that was included in the answer of this question (which leadsd to a change request posted by a user, and closed by Microsoft), a Microsoft engineer posts the following information:

    "There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb."

    But you don't get the IDENTITY reseed that Truncate gives.

    DECLARE @bob-2 TABLE(

    tid INT IDENTITY(1,1) PRIMARY KEY,

    tval VARCHAR(50))

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    INSERT @bob-2 DEFAULT VALUES

    SELECT * FROM @bob-2

    DELETE @bob-2

    INSERT @bob-2 DEFAULT VALUES

    SELECT * FROM @bob-2

    That said, I found reading the comments kind of amusing.

    "We're using table variables because of the performance increase from temp tables"

    Ummm

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Nice question. I have never considered truncating a table variable before.

  • Now I have one more reason to love table variables 😉

  • Hmmm, learned something new today ... Thanks!

  • I didn't know we could have an identity column in a table variable.

    And that it can't be reset. I was expecting it wouldn't be possible to truncate the table though.

    I wonder what you guys use the most: temp tables or table variables in terms of performance. I know that temp tables can have additional indexes, etc. but it can also have the effect of locking tempdb while it's being created or altered.

    Anyway, that was an excellent question for me. 🙂

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Great question. Learned something new, now I have to remember that the next time I use table variables.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • codebyo (8/17/2012)


    I wonder what you guys use the most: temp tables or table variables in terms of performance.

    It depends on the context. Table variables can have significant performance advantages when it comes to creating them very quickly, and temporary tables have some very strange behaviours. Most times, where an execution plan choice depends on the contents of a temporary object, I choose temporary tables, but I am *very careful* about the details. (More information at http://bit.ly/TempTables and http://bit.ly/TempCaching)

    I know that temp tables can have additional indexes, etc. but it can also have the effect of locking tempdb while it's being created or altered.

    The 'locking tempdb' thing hasn't been an issue since SQL Server 6.5, though the myth has lasted extremely well 🙂

  • SQL Kiwi (8/21/2012)


    The 'locking tempdb' thing hasn't been an issue since SQL Server 6.5, though the myth has lasted extremely well 🙂

    I wasn't aware it was a myth. :w00t:

    I failed to answer one question related to this at a job interview and there were three experts in that room telling me that that was a potential issue in their production servers and I should have known about tempdb locking and temporary tables. Note: the company is world leader in mobile platform software.

    They said I failed the interview because I didn't know that.

    Thank you for the correction. I wouldn't know it is a myth if it wasn't for you. 🙂

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Hugo Kornelis (8/16/2012)


    sestell1 (8/16/2012)


    A good question. This is a drawback to using table variables for temporary storage that you need to remember to work around.

    Actually, this is not a drawback and you don't have to work around it. In the link that was included in the answer of this question (which leadsd to a change request posted by a user, and closed by Microsoft), a Microsoft engineer posts the following information:

    "There is no difference between DELETE without WHERE clause and TRUNCATE TABLE on temporary tables or table variables. This is due to the behavior of logging in tempdb."

    Yes, but the MS guy got it wrong, there is a difference. For temporary tables, TRUNCATE TABLE resets the identitity sequence, and delete does not reset it. Even worse, the other method of resetting the identity sequence (DBCC CHECKIDENT) is also invalid for table variables, so for a table variable there's no way of emptying the table and restarting the identity sequence.

    Tom

  • Thanx learn something TODAY;-)

    Neeraj Prasad Sharma
    Sql Server Tutorials

Viewing 13 posts - 16 through 27 (of 27 total)

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