Table Variable

  • TomThomson

    SSC Guru

    Points: 104773

    Hugo Kornelis (8/31/2012)


    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.

    I think that's a bad mistake. You are encouraging MS to give us rotten buggy documentation and unhelpful meaningless error messages. If of course you like bad documentation and sloppy error messages, you've done the right thing, but I didn't think you had those tastes.

    Tom

  • Ron McCullough

    SSC Guru

    Points: 63877

    See next post

    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]

  • Ron McCullough

    SSC Guru

    Points: 63877

    L' Eomot Inversé (9/2/2012)


    Hugo Kornelis (8/31/2012)


    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.

    I think that's a bad mistake. You are encouraging MS to give us rotten buggy documentation and unhelpful meaningless error messages. If of course you like bad documentation and sloppy error messages, you've done the right thing, but I didn't think you had those tastes.

    Now the effort to do it correctly would be WHAT

    Link to BOL SET IDENTITY_INSERT (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms188059(v=sql.90).aspx

    database_name

    Is the name of the database in which the specified table resides.

    schema_name

    Is the name of the schema to which the table belongs.

    table

    Is the name of a table with an identity column.

    -- suggested editing

    Use with a table variable will result in an error message

    -- end suggested editing

    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]

  • marlon.seton

    SSCrazy

    Points: 2623

    Andrew Watson-478275 (8/31/2012)


    Another point - the question would have required much more thought if one of the answers had been "1 SCREW, 2 SHOVEL, 3 SAW", which would have been the correct answer if it wasn't for the problem with Identity Insert on table variables.

    ORDER BY was not specified so the actually order of the three items in the two wrong answers is surely irrelevant, no?

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    It's not the order that I was questioning, but the combination of string and identity. If it wasn't for the "feature" being highlighted, "Shovel" would be in there with an ID of 2, but this wasn't one of the given answers.

  • udayroy15

    Ten Centuries

    Points: 1076

    nice question...........

    it is table variable........not temp or static table

Viewing 6 posts - 31 through 36 (of 36 total)

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