January 11, 2011 at 5:31 am
Hugo Kornelis (1/11/2011)
Did I misinterpret the documentation? Or is Tom incorrect? (Christian's test code suggests the latter).
I think you are right - I got it wrong. Mea maxima culpa - I should have checked thoroughly instead of relying on (evidently faulty) memory.
Perhaps Steve can correct the answer and the explanation before more people are bitten by my error.
Tom
January 11, 2011 at 7:50 am
DugyC (1/11/2011)
God damn it!! I would have got this one right had I read the MSDN linked page properly, it was my reference from google.I assumed VARCHAR & NVARCHAR would be treated the same, instead of reading further down and seeing that NVARCHAR is unaffected.
Lost point, but through my own stupidity... that is frustrating!
:rolleyes:
/agree
did the same thing...
January 11, 2011 at 8:22 am
Tom.Thomson (1/11/2011)
Hugo Kornelis (1/11/2011)
Did I misinterpret the documentation? Or is Tom incorrect? (Christian's test code suggests the latter).I think you are right - I got it wrong. Mea maxima culpa - I should have checked thoroughly instead of relying on (evidently faulty) memory.
Perhaps Steve can correct the answer and the explanation before more people are bitten by my error.
That would be great considering the only ones that have got the question right or those that understand the SET command wrong. :w00t:
I noticed that the last question I submitted left the question as it was after the original submission. It was changed after that to be something differant, but the original with mistakes is what was posted as QOTD. Maybe Steve can fix that as well so we stop having so many of these kind of QOTD's.
January 11, 2011 at 8:48 am
Would have got it right except I was thrown by the apparent conflict that the question was for SQL Server 2008 but the last option clearly asks about SQL Server 2000. Why would I select a SQL2000 option if the question is about SQL2008?
Other than that, great question. I learned some new stuff today.
January 11, 2011 at 9:19 am
Tough question, thanks for posting. This one should have been worth more than one point.
Adam Sottosanti
January 11, 2011 at 9:31 am
Richard Sisk (1/11/2011)
Would have got it right except I was thrown by the apparent conflict that the question was for SQL Server 2008 but the last option clearly asks about SQL Server 2000. Why would I select a SQL2000 option if the question is about SQL2008?Other than that, great question. I learned some new stuff today.
You may need to reread the question & some of the comments.
First, there was no SQL2000 option. Instead the option was that the query performance on SQL Server 2008 for a query using TOP with ANSI_PADDING OFF may be worse than with SQL Server 2000. SQL Server 2000 is referenced in this option, but the topic is the performance of TOP with ANSI_PADDING OFF in SQL Server 2008.
Second, two answers of the QOTD are incorrect.
a) The usage of an index on a computed column, and
b) the possibility to alter an index on a computed column.
Lesson learned: Do not trust Microsofts documentation 😉
Best Regards,
Chris Büttner
January 11, 2011 at 10:17 am
I have to disagree with one part of your "correct" answer set. You say that existing indexes can be used, and that cost me a point for this question.
Microsoft says quite clearly at http://msdn.microsoft.com/en-us/library/ms190356.aspx that "SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist" when ANSI_PADDING is OFF, which means existing indexes can *NOT* be used.
January 11, 2011 at 2:03 pm
Christian Buettner-167247 (1/11/2011)
Second, two answers of the QOTD are incorrect.a) The usage of an index on a computed column, and
b) the possibility to alter an index on a computed column.
Lesson learned: Do not trust Microsofts documentation 😉
It was certainly wrong on your (a), and the MS documentation is right on that point (so the error in the question was my failure).
On (b) things are more confused - it seems to be possible to do some things to indexes on a computed column when ansi padding is off. Other things are not possible, though; the MS documentation says it all doesn't work, wbut you discovered by experiment that that is incorrect, so there's no telling what does work and what doesn't except by experiment.
I knew some index changes couldn't be made when ANSI padding was off (a friend had problems and got me to look at what was going wrong - she now always sets all the sql-92 related settings after I gave her a strong lecture) so I believed the documentation when it said no index changes could be made to indexes on computed a computed column when the option was set off. I guess I should have done some experimentation to verify that, as I'm fairly used to documentation being wrong, or overgeneralising, or saying something can't be done simply because it's a bit risky (eg some things will fail sometimes and work sometimes because of race conditions or other sources of indeterminacy).
Tom
January 11, 2011 at 2:05 pm
rawagoner (1/11/2011)
I have to disagree with one part of your "correct" answer set. You say that existing indexes can be used, and that cost me a point for this question.Microsoft says quite clearly at http://msdn.microsoft.com/en-us/library/ms190356.aspx that "SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist" when ANSI_PADDING is OFF, which means existing indexes can *NOT* be used.
Yes, you are right. I've sent Steve a PM asking if he can fix it.
Tom
January 11, 2011 at 3:46 pm
Spent 30 mins on this one but still got incorrect answer due to issue with answers but still an excellent question - ANSI PADDING was something I never really spent much thought on before....
January 11, 2011 at 9:12 pm
Indexes on computed columns will not be used - is also true.
From Books Online article "SET (Transact-SQL)"
"When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.
If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist. "
It would be nice if I can get my point. Thanks.
January 12, 2011 at 7:29 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 12, 2011 at 1:23 pm
I have corrected the question to note that indexes on computed columns will not be used.
Because of this, I have also awarded back points to everyone that has answered this to date.
January 18, 2011 at 10:14 am
Thanks for the question--got it wrong as I missed the indexes on computed columns will not be used.
However, I'm baffled about the statistics, as only 25 % of the responders apparently ticked this option, but there are 73 % of correct answers. That's either higher mathematics (I don't have a grasp on) or the statistics did not get updated.
Regards,
Michael
January 19, 2011 at 2:55 am
michael.kaufmann (1/18/2011)
Thanks for the question--got it wrong as I missed the indexes on computed columns will not be used.However, I'm baffled about the statistics, as only 25 % of the responders apparently ticked this option, but there are 73 % of correct answers. That's either higher mathematics (I don't have a grasp on) or the statistics did not get updated.
Michael, the reason for this is in the message right above yours. Steve awarded points back to people who had already replid. That means that at that moment, the percentage of correct answer went up to 100%, but the distribution of selected answers remained unchanged.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply