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


ANSI Padding


ANSI Padding

Author
Message
paul.knibbs
paul.knibbs
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: 2994 Visits: 6235
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
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: 4083 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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2764 Visits: 1134
Good question - I see only 8% have got the correct answer at the moment.
Toreador
Toreador
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3118 Visits: 8088
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13129 Visits: 12167
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
Tom Thomson
Tom Thomson
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17444 Visits: 12331
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
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: 5336 Visits: 72521
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1742 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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 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 (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 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