Table Variable

  • Comments posted to this topic are about the item Table Variable

  • 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

  • Nice question, thanks.

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

  • 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 🙂

  • 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

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

  • 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

  • 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]

  • 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

  • Great learning question for me.

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

  • 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/

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

    Good question -- and good answer choices!

  • 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 27 total)

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