Inserted Identity

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    Comments posted to this topic are about the item Inserted Identity

  • Paul White

    SSC Guru

    Points: 150442

    A good question, though the example was more complex than it needed to be.

  • Carlo Romagnano


    Points: 21987

    INSERTED pulls from what is entered into table, not from the select list.

    Of course!

  • Kameswari Aravindh


    Points: 2380

    Good Question. 🙂

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104773

    Not a bad question. Just a bit too easy.

    I can't imagine how anyone could select the answer 4,5,6 because even if they mistakenly look at the select list instead of the inserted data the only column named id in the select list is the id column from deleted, which contains the values 1,2,3. Maybe if the id column of define had been declared as "id id INT IDENTITY(4,1)" instead of "id id INT IDENTITY(1,1)" there would have been a better chance of people erroneously selecting the wrong answer 4,5,6. Only 3% of people have selected 4,5,6 so far, and I suspect that proportion will not increase as more people answer - perhaps it will decrease.

    I suppose some might go for the "error" answer if they think that "define" is a reserved word that would need to be quoted when used as a table name, but I can't think of any T-SQL syntax that uses "define" in a way that would require it to be a reserved word so I don't know why anyone would think that. But 22% so have have thought that (or derampt up some other imaginary error).

    If it weren't for the fact that 22% of answers so far are "error" I would be predicting a high 90s correct answer rate for this one.


  • steve.smith 42985

    SSC Journeyman

    Points: 75

    Although the answer of what was intended is obviously 1,2,3 it does depend where you run this.

    Try running it on the AdventureWorks DB of a fresh SQL 2005 Install and it will indeed error:

    Msg 2714, Level 16, State 6, Line 5

    There is already an object named 'define' in the database.

    Msg 207, Level 16, State 1, Line 10

    Invalid column name 'value'.

  • Kwex

    Default port

    Points: 1407

    Good question ... a bit too much text though considering that the final focus was on the IDENTITY column 🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy question, thanks.

    Need an answer? No, you need a question
    My blog at
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thomas Abraham


    Points: 10761

    SQLkiwi (8/25/2011)

    A good question, though the example was more complex than it needed to be.

    Agreed. Although it did simulate a lot of maintenance programming I've had to do. Reminded me of situations where you'd wonder just what the heck is this guy trying to do here?

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

  • skrilla99


    Points: 5795

    Ha ha! Oops!

    I got tripped up by poor reading. I would have sworn there were three columns being inserted into t2 and figured it would throw an identity insert error... Now that I read it again, that sneaky col has disappeared! I'll chalk this up to bleary eyes to save my ego.

    Off for coffee!

    Thanks for the question!


  • callie97

    SSC Eights!

    Points: 961

    I got an error also on the t1.value column. If you change it to t1.VALUE, as it is in the table definition, then I got 1,2,3 as I thought. All depends on how you defined your server to handle case differences.

  • hakim.ali

    SSCarpal Tunnel

    Points: 4237

    Imho, the example was way more complex than it needed to be, so much so that it took away from the point of the exercise.

    Hakim Ali

  • Daniel Bowlin


    Points: 34566

    I must be tired this morning, I had to read the example 4 or 5 times to figure out what was going on. Good question, thanks.

  • Cliff Jones


    Points: 10517

    Daniel Bowlin (8/26/2011)

    I must be tired this morning, I had to read the example 4 or 5 times to figure out what was going on. Good question, thanks.

    Yes, I had to stare at it for a while to figure out what was going on also.

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

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