April 2, 2014 at 6:54 am
Glad I'm not the only one who did not get an error when running this. I didn't see an error in the code so I had to guess at the last answer choice and guessed wrong.
April 2, 2014 at 7:04 am
I agree with what most of you have said, and running the code did not generate any errors. I guess the confusion comes from the statement "You're about to test updates and work on performance". This of course doesn't necessarily mean that you have enabled the execution plan, although I know it might be the first place I'd look.... So this was the trap in this question, you had to "assume" that by saying Hey! I'm working on performance, you had just turned on the execution plan.
Like many others, 4 answers right and the last one was wrong due to the fact that I had to guess it. Not making up excuses, just sayin' 😉
April 2, 2014 at 7:10 am
With respect - I know that preparation of a QoTD can be quite tricky and time consuming but this QoTD has the same problem as the last one; it's dealing with settings which are "not normal"
The default for that setting is false, but it in this case it was enabled
This information hits the nail - but it was in the answer not in the question 🙁
4 of 5 correct; last wrong because of missing information!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
April 2, 2014 at 7:21 am
I'm using SQL 2012 Developer Edition for testing. Run this from the question text:
CREATE TABLE Questions
(
QuestionID INT
, QuestionTitle VARCHAR(100)
, datechanged DATETIME
, IsApproved BIT DEFAULT 0
)
go
CREATE TRIGGER updateQuestions ON dbo.Questions
FOR UPDATE
AS
UPDATE Q
SET Q.datechanged = GETUTCDATE()
FROM inserted i
INNER JOIN dbo.Questions Q
ON I.QuestionID = q.QuestionID
go
INSERT INTO Questions
( QuestionID
, QuestionTitle
, datechanged
)
VALUES
( 1
, 'Select me!'
, GETUTCDATE()
)
go
INSERT INTO Questions
( QuestionID
, QuestionTitle
, datechanged
)
VALUES
( 2
, 'Tables and Columns, Oh My'
, GETUTCDATE()
)
go
Then this as the secret sauce:
sp_configure 'disallow results from triggers', 1
reconfigure
And then Control-M to enable execution plans.
And then, the final update from the question text:
UPDATE Questions
SET IsApproved = 0
WHERE QuestionID IN ( 1, 2 )
Yields for me:
(2 row(s) affected)
(1 row(s) affected)
Msg 524, Level 16, State 1, Procedure updateQuestions, Line 4
A trigger returned a resultset and the server option 'disallow results from triggers' is true.
April 2, 2014 at 7:26 am
I tried to give all the clues I could without giving it away - tough to do that. Putting the easy answers in was designed to make it look like an easy question - it wasn't! I was trying to get you to keep saying "why" on the tough one, why would that happen?
I appreciate the comments, good and bad, and any ideas you have for making it better without making it easier!
April 2, 2014 at 7:49 am
I thought the question was a good one. However I too was trying to guess the 5th choice. I reread the question and it says
You're about to test updates and work on performance (you haven't added any indexes yet) when the gong rings for the stand up.
This suggested to me that performance testing had not yet begun. This would generally indicate you have not yet looked at the execution plan. I couldn't for the life of me figure out the three rows thing because the messages would have said 2 row(s) and 2 row(s). One for the update and the other for the trigger. Not sure how 3 came from that.
I think a simple text change to
You started looking at performance when the gong rings...
Still would have been somewhat ambiguous but would have offered a hint.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2014 at 7:53 am
By the way what is this Vanilla trigger?
April 2, 2014 at 7:59 am
It's unclear from the reqirements whether updating [IsApproved] column should set [datechanged], or if this should only apply to [QuestionTitle].
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2014 at 8:19 am
Eric M Russell (4/2/2014)
It's unclear from the reqirements whether updating [IsApproved] column should set [datechanged], or if this should only apply to [QuestionTitle].
As the design stands at the moment, it would even trigger on change of QuestionID, which could make for some interesting results!
April 2, 2014 at 8:41 am
Given the requirements, I don't think there is anything functionally wrong with the table and trigger as is, except that the QuestionID needs to be indexed and the primary key.
Also, as a side note, SQL Server could really use a real date/time based alternative to TIMESTAMP datatype to prevent developers from feeling the need to do this type of thing with triggers. The current TIMESTAMP datatype is more of a unique row version stamp that is sequential but not date/time.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2014 at 11:32 am
I believe that there's an error in the logic of the trigger since there's no change in the data. If the information hasn't changed, then the datechanged value shouldn't change either.
April 2, 2014 at 11:35 am
Luis Cazares (4/2/2014)
I believe that there's an error in the logic of the trigger since there's no change in the data. If the information hasn't changed, then the datechanged value shouldn't change either.
Technically speaking the data did change. The columns were updated. The new values however do equal the old values. This one may be a case of semantics but the sql engine doesn't look at the existing values and not update a column just because it is the same value. It simply overwrites the values with the new ones, which in this case they are the same. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2014 at 11:38 am
Eric M Russell (4/2/2014)
Given the requirements, I don't think there is anything functionally wrong with the table and trigger as is, except that the QuestionID needs to be indexed and the primary key.Also, as a side note, SQL Server could really use a real date/time based alternative to TIMESTAMP datatype to prevent developers from feeling the need to do this type of thing with triggers. The current TIMESTAMP datatype is more of a unique row version stamp that is sequential but not date/time.
The data definately changed, but it's not clear which columns are information. If business doesn't like how it works, then this one's a change request, not a bug given the requirements such as they are.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2014 at 11:43 am
Sean Lange (4/2/2014)
Luis Cazares (4/2/2014)
I believe that there's an error in the logic of the trigger since there's no change in the data. If the information hasn't changed, then the datechanged value shouldn't change either.Technically speaking the data did change. The columns were updated. The new values however do equal the old values. This one may be a case of semantics but the sql engine doesn't look at the existing values and not update a column just because it is the same value. It simply overwrites the values with the new ones, which in this case they are the same. 😉
That's why I avoided the word update. The column was updated and the data was replaced with the same values. To me, there's no change in the data even if there's a change underneath. It's just an issue on what values do we feel are relevant, last update or last change.
April 2, 2014 at 11:59 am
It's just an issue on what values do we feel are relevant, last update or last change.
That is pretty much what I said but yours is much easier to read and understand. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply