Table Variable

  • MissTippsInOz

    Hall of Fame

    Points: 3158

    Comments posted to this topic are about the item Table Variable

  • Lokesh Vij

    SSChampion

    Points: 10836

    Good one. Learnt something obvious 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 842

    It is also worth noting that you have no method of resetting the identity counter for the table.

    Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂

  • Lokesh Vij

    SSChampion

    Points: 10836

    Keld Laursen (VFL) (8/15/2012)


    It is also worth noting that you have no method of resetting the identity counter for the table.

    Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂

    Very rightly pointed out Keld. Truncate resets the identity counter for the table..

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 842

    Lokesh Vij (8/15/2012)


    Keld Laursen (VFL) (8/15/2012)


    It is also worth noting that you have no method of resetting the identity counter for the table.

    Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂

    Very rightly pointed out Keld. Truncate resets the identity counter for the table..

    Which were my point exactly: No truncate, no reset. Other DML to reset the counter will also fail.

  • Lokesh Vij

    SSChampion

    Points: 10836

    Keld Laursen (VFL) (8/15/2012)


    Lokesh Vij (8/15/2012)


    Keld Laursen (VFL) (8/15/2012)


    It is also worth noting that you have no method of resetting the identity counter for the table.

    Thanks for the question. I were fairly sure that I had it right before submitting, but the other choices listed had me re-check. And I were right 🙂

    Very rightly pointed out Keld. Truncate resets the identity counter for the table..

    Which were my point exactly: No truncate, no reset. Other DML to reset the counter will also fail.

    I was just trying to re-iterate, as Table variable does not allow truncate to be used, identity counters will not reset 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • This was removed by the editor as SPAM

  • Ron McCullough

    SSC Guru

    Points: 63877

    Stewart "Arturius" Campbell (8/16/2012)


    Good question, thanks.

    Learned something here.

    +1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thomas Abraham

    SSChampion

    Points: 10761

    Nice question. Thanks for the contribution. Never thought of truncating a temp table. But, it reminded me about limitations of this feature.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Great learning question for me.

  • sestell1

    SSChampion

    Points: 10230

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

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    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."


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • sknox

    SSChampion

    Points: 12284

    Had to double-check the use of IDENTITY in table variables.

    Good question -- and good answer choices!

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thomas Abraham (8/16/2012)


    Nice question. Thanks for the contribution. Never thought of truncating a temp table. But, it reminded me about limitations of this feature.

    + 1



    Everything is awesome!

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

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