Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANSI Padding


ANSI Padding

Author
Message
paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1974 Visits: 6214
I got it right apart from the last option--the reference I was looking at didn't mention anything about TOP performance being affected by this. Not greatly saddened because I don't think it likely I'll ever run into this anyway, but more information is always handy to have!
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 3889
Toreador (1/11/2011)
As for the option about performance relative to SQL2000 - that's an 11-year-old unsupported version[...]

SQL Server 2000 is not unsupported. Only the mainstream support has ended.

http://blogs.msdn.com/b/sqlreleaseservices/archive/2008/02/15/end-of-mainstream-support-for-sql-server-2005-sp1-and-sql-server-2000-sp4.aspx

Best Regards,

Chris Büttner
paul s-306273
paul s-306273
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1923 Visits: 1060
Good question - I see only 8% have got the correct answer at the moment.
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 8064
Christian Buettner-167247 (1/11/2011)
SQL Server 2000 is not unsupported. Only the mainstream support has ended.


So it's supported, but only if we pay for it.

In my book, that means it's unsupported ;-)
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
Usually, when I come to the forum while thinking that the question is not 100% acccurate, I encounter lots of similar remakrs already. Today, nobody has yet raised the point I want to make - so I'm beginning to doubt myself. Please tell me if I misread Books Online...

On the page referenced from the question's explanation, it says:
For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET (Transact-SQL).

Following that page and scrolling down to the last paragraph, I read:
If any one of these options is not set to the required values, (...) 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.

My interpretation of this text is that indexes on computed columns will not be used when ANSI_PADDING and various other SET options are not set to the required value. But the explanation in the question says the exact reverse: "(...) but existing indexes can be used" (emphasis is mine).

Did I misinterpret the documentation? Or is Tom incorrect? (Christian's test code suggests the latter).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
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

mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
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...



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
SanDroid
SanDroid
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: 1414 Visits: 1046
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.
Richard Sisk
Richard Sisk
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1989 Visits: 210
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.
Adam Sottosanti
Adam Sottosanti
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 404
Tough question, thanks for posting. This one should have been worth more than one point.

Adam Sottosanti
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