Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

ANSI Padding Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 9:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #1045979
Posted Tuesday, January 11, 2011 10:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 16, 2012 10:40 AM
Points: 1,399, Visits: 205
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.
Post #1046002
Posted Tuesday, January 11, 2011 2:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:18 PM
Points: 7,930, Visits: 9,654
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
Post #1046154
Posted Tuesday, January 11, 2011 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:18 PM
Points: 7,930, Visits: 9,654
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
Post #1046155
Posted Tuesday, January 11, 2011 3:46 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:01 PM
Points: 412, Visits: 963
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....
Post #1046191
Posted Tuesday, January 11, 2011 9:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:21 PM
Points: 3,143, Visits: 139
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.
Post #1046242
Posted Wednesday, January 12, 2011 7:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:32 PM
Points: 18,068, Visits: 16,111
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
Post #1046477
Posted Wednesday, January 12, 2011 1:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Saturday, December 20, 2014 3:11 PM
Points: 31,368, Visits: 15,837
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1046770
Posted Tuesday, January 18, 2011 10:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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
Post #1049512
Posted Wednesday, January 19, 2011 2:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:43 AM
Points: 6,149, Visits: 8,409
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1049861
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse