Table Variable

  • Thomas Abraham

    SSChampion

    Points: 10761

    bitbucket-25253 (8/31/2012)


    Rather suprised at the number of incorrect answers. Would have thought that the percent correct would be closer to 100

    You overestimate how awake some of us are in the AM. 🙂

    Actually, I have never even thought of using identity insert for a table variable. In what situations have you had to use it?

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

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    DugyC (8/31/2012)


    IgorMi (8/31/2012)


    Nice question!

    It is good to know the advantages and disadvantages of temp tables and temp variable tables

    The following code will work fine:

    create table #tmp(SlNo int identity, Name varchar(200))

    INSERT INTO #tmp(Name) Values('SCREW')

    INSERT INTO #tmp(Name) Values('HAMMER')

    INSERT INTO #tmp(Name) Values('SAW')

    DELETE FROM #tmp WHERE SlNo = 2

    SET IDENTITY_INSERT #tmp ON

    INSERT INTO #tmp(SlNO,Name) Values(2,'SHOVEL')

    SELECT * FROM #tmp

    Thanks

    IgorMi

    ...giving the following output...

    1 SCREW

    2 SHOVEL

    3 SAW

    ...whereas if you use a permenant table 'tmp', the output is subtly different...

    1 SCREW

    3 SAW

    2 SHOVEL

    Great question, and nice and easy for a Friday. Thanks.

    Thank you for your reply

    However consider that "The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified"

    I run the above code with #tmp and tmp tables, run it on sql server 2005 and 2008 and the same result:

    SlNo Name

    1 SCREW

    2 SHOVEL

    3 SAW

    Regards

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Ron McCullough

    SSC Guru

    Points: 63877

    sknox (8/31/2012)


    bitbucket-25253 (8/30/2012)


    Nice question on the basics

    ...A better supporting document/respected DBA is this blog posting by Steve Jones

    http://www.sqlservercentral.com/blogs/steve_jones/2012/08/03/identity-insert-and-table-variables-connect-issue/

    Agreed. Also, if you haven't voted on the Connect submission for this issue, please consider doing so:

    https://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables

    Voted

    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]

  • sestell1

    SSChampion

    Points: 10230

    Nice, I new the answer and managed to click on the correct answer as well.

    Today is already shaping up to be better than yesterday. 🙂

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    bitbucket-25253 (8/31/2012)


    sknox (8/31/2012)


    bitbucket-25253 (8/30/2012)


    Nice question on the basics

    ...A better supporting document/respected DBA is this blog posting by Steve Jones

    http://www.sqlservercentral.com/blogs/steve_jones/2012/08/03/identity-insert-and-table-variables-connect-issue/

    Agreed. Also, if you haven't voted on the Connect submission for this issue, please consider doing so:

    https://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables

    Voted

    I voted as well - but I voted it down. I fail to see any realistic situation where I would need to be able to use IDENTITY_INSERT on a table variable, so I'd rather see the SQL Server Engineering team spend their time on other features.


    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/

  • Dana Medley

    SSCertifiable

    Points: 6764

    sknox (8/31/2012)


    Agreed. Also, if you haven't voted on the Connect submission for this issue, please consider doing so:

    https://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables

    You got my vote!

    This was a great easy question for Friday and leading up to the holiday.

    I would have to agree with Thomas as well on the number of incorrect answers; some people are not just morning persons and it's at least once a week where I just zombie through QotD. :hehe:



    Everything is awesome!

  • Error Handler

    Ten Centuries

    Points: 1086

    Nice Question to end up the week....

    Best,
    Naseer Ahmad
    SQL Server DBA

  • mtassin

    SSC-Insane

    Points: 23099

    Hugo Kornelis (8/31/2012)


    I voted as well - but I voted it down. I fail to see any realistic situation where I would need to be able to use IDENTITY_INSERT on a table variable, so I'd rather see the SQL Server Engineering team spend their time on other features.

    I have to agree... considering what table variables are... if you're going to do enough work on the table that you need to be able to set identity_insert on, you probably also can benefit from the statistics and other things you get with a temp table or permanent one.



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

  • Narud

    SSCrazy

    Points: 2826

    Opposite of what happened yesterday, and still proudly, again I'm part of the 77%, but now with the right answer. Some days are better than others 😎

  • EL Jerry

    SSCertifiable

    Points: 7053

    Good, easy question to end the week.

    I agree with Hugo that there would be no reason to use IDENTITY in a table variable, but it is up to Microsoft to fix it or not.

    Happy weekend everyone, happy long weekend to you guys in the US and Canada!!!

    Edit:added Canada, they also have a holiday.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

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

  • Ken Wymore

    SSCoach

    Points: 16592

    Good question. I recalled a similar question recently and so I got this right.

    I don't know why you would need to do identity_insert on a table variable so I have to agree with Hugo on voting against this fix. There are definitely better items for the SQL Server team to work on.

    Happy Friday

  • WayneS

    SSC Guru

    Points: 95384

    I'm curious as to why a SQL 2000 reference was used (although the 2012 reference states the same, this one is specifically for 2000).

    There are a bunch of limitations and restrictions of table variables at table (Transact-SQL) - I think that if using IDENTITY_INSERT is prohibited, then it ought to be listed here. Seems like a simple enough thing to make the documentation correct.

    I'll vote the connect item up, but for updating the documentation.

    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

  • aochss

    SSCommitted

    Points: 1677

    Oops, I was in the percent, who answered it wrong. I totally glossed over the table variable and that the second insert said shovel instead of hammer.

    It falls into the same area that one skips certain words/letters when skimming an article.

    The lesson here is to read the scripts you are debugging/reading very carefully and NOT assume anything.

    Thanks for the reminder to be more careful. Great question just for that reminder.

    Anton

  • TomThomson

    SSC Guru

    Points: 104773

    bitbucket-25253 (8/30/2012)


    Nice question on the basics[rant]

    Unfortunately the cited documentation does not support the correct answer.

    A better supporting document/respected DBA is this blog posting by Steve Jones

    http://www.sqlservercentral.com/blogs/steve_jones/2012/08/03/identity-insert-and-table-variables-connect-issue/

    [/rant]

    I agree, this is certainly bad implementation (if the feature is supposed to be not supported, the error message should tell us that and not just produce a meaningles syntax error message; if it is supposed to be supported it should work, not produce an error) and perhaps bad documentation too.

    I hope lots of people will follow the link in Steve's blog posting and vote up the connect item.

    Tom

  • TomThomson

    SSC Guru

    Points: 104773

    arthurolcot (8/31/2012)


    Nice question, although i think the answers made it easier just through a process of elimination. 🙂

    yes, nice question but terrible selection of answer options. If the option 1 Saw, 3 Screw, 2 Shovel had been included it would have been much easier for people to get it wrong.

    Tom

Viewing 15 posts - 16 through 30 (of 36 total)

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