SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANSI Padding


ANSI Padding

Author
Message
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5349 Visits: 3889
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 Wink

Best Regards,

Chris Büttner
robert.wagoner
robert.wagoner
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1399 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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26299 Visits: 12506
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 Wink


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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26299 Visits: 12506
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

JayK
JayK
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 1133
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....
Dr. Diana Dee
Dr. Diana Dee
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3433 Visits: 143
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68067 Visits: 18570
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

Steve Jones
Steve Jones
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: Administrators
Points: 149430 Visits: 19448
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
My Blog: www.voiceofthedba.com
michael.kaufmann
michael.kaufmann
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1589 Visits: 1082
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
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19039 Visits: 12426
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search