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 2:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 1,631, Visits: 5,578
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!
Post #1045748
Posted Tuesday, January 11, 2011 2:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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
Post #1045751
Posted Tuesday, January 11, 2011 3:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
Good question - I see only 8% have got the correct answer at the moment.
Post #1045763
Posted Tuesday, January 11, 2011 3:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 1,739, Visits: 6,351
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
Post #1045765
Posted Tuesday, January 11, 2011 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 5,969, Visits: 8,228
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
Post #1045774
Posted Tuesday, January 11, 2011 5:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 8,707, Visits: 9,255
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
Post #1045821
Posted Tuesday, January 11, 2011 7:50 AM


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: Friday, August 22, 2014 7:04 AM
Points: 3,673, Visits: 72,433
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!



/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
Post #1045908
Posted Tuesday, January 11, 2011 8:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.

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.
Post #1045929
Posted Tuesday, January 11, 2011 8:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 16, 2013 8:08 AM
Points: 1,987, 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.
Post #1045950
Posted Tuesday, January 11, 2011 9:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 4:16 PM
Points: 143, Visits: 359
Tough question, thanks for posting. This one should have been worth more than one point.

Adam Sottosanti
Post #1045971
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse