August 25, 2011 at 8:12 pm
Comments posted to this topic are about the item Inserted Identity
August 25, 2011 at 9:11 pm
A good question, though the example was more complex than it needed to be.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 26, 2011 at 12:47 am
INSERTED pulls from what is entered into table, not from the select list.
Of course!
August 26, 2011 at 12:47 am
Good Question. 🙂
August 26, 2011 at 1:21 am
This was removed by the editor as SPAM
August 26, 2011 at 1:26 am
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.
Tom
August 26, 2011 at 2:54 am
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'.
August 26, 2011 at 3:23 am
Good question ... a bit too much text though considering that the final focus was on the IDENTITY column 🙂
August 26, 2011 at 5:12 am
Easy question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2011 at 5:39 am
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
August 26, 2011 at 6:38 am
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!
-Dan
August 26, 2011 at 6:56 am
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.
August 26, 2011 at 6:57 am
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
www.sqlzen.com
August 26, 2011 at 7:42 am
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.
August 26, 2011 at 8:26 am
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 32 total)
You must be logged in to reply to this topic. Login to reply