Right, I've had enough of poorly-written practice questions!

Question

Right, I've had enough of poorly-written practice questions!

derek.colley

SSCrazy Eights

Points: 8040
More actions
August 24, 2012 at 10:48 am

#280781

<rant>
Is it just me, or are some of the Microsoft practice exams and texts hopelessly inaccurate or worse, misleading and biased?
I refer specifically to the set text for the 70-432 exam for 2008.
I also refer specifically to the 5-page 'errata' document published by MS full of corrections to this book.
The practice tests on the companion CD aren't up to much either. Example:
'You are the database administrator at Lucerne Publishing, a French company based in Paris. Users are complaining that results are not being sorted properly. How do you fix the problem and guarantee that data will always be sorted properly from that point forward, regardless of the instance where the database is located?'
A. Change the collation sequence for the table
B. Change the collation sequence for the instance
C. Change the collation sequence for the column
D. Change the collation sequence for the database
The 'correct' answer is C - and it is correct. The collation can indeed be set at column-level without fault. However, answers A and D are equally correct according to the terms of the question, since the key phrase is:
'... regardless of the instance where the database is located?'
Which presumably means that if the database was located to an instance with a different collation setting, all database, table/object and column levels of collation would achieve this effect. So A, C and D are technically correct!
There are many examples of this kind of mistake all the way through - there's another question too lengthy to reproduce here that asks for the correct syntax (from a multi-choice of 4 options) for backing up a database to tape, wiping the tape beforehand. The 'correct' answer is some syntax that inexplicably includes a MIRROR TO clause to another tape device(???). Another equally correct answer is the same block, but with the MIRROR TO target being a disk.
I've checked each answer I get wrong with BOL and other sources and in some (not all - I'm not THAT good!) cases there is simply no justification for a limited set of one or more answers when other answers can be equally correct given the circumstances.
And don't get me started on their liberal use of '...using the least amount of effort...'. If I wanted to use the least amount of effort I'd be visiting the job centre every week.
Grrr....
</rant>
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

derek.colley (8/24/2012)
Which presumably means that if the database was located to an instance with a different collation setting, all database, table/object and column levels of collation would achieve this effect. So A, C and D are technically correct.

No they aren't.

Instance collation only affects the collation of the system databases and the default collation of any databases when they are created

Database collation only affects the default collation of columns when they are created.

Tables don't have a collation.

If I have a column with an incorrect collation (let's say it's somehow binary and it should be latin1_general) and hence sorts in a non-intuitive order changing the instance collation will have no effect whatsoever (well, it'll require downtime to implement, but the sort will be unaffected). Changing the database collation will have no effect because it's an existing column in an existing table. It'll ensure that any new tables created with string columns have correct collation, but won't affect the current tables.

The only way to fix an incorrect collation on a column of an existing table is to change the collation at a column level.

Well, I could also add a COLLATE clause to the order by of the query, that would fix it too, but that's not an option in the question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Scott D. Jacobson SSCertifiable Points: 6089 More actions · Answer 2

Derek, I have a tendency to agree with you. In fact, stuff like this that I've seen makes me think that would have a hard time passing these exams. Either running out time due to agonizing over a question like this or simply answering "wrong" b/c it doesn't match the answer sheet even if it's totally correct. The question is also a little vague. "Users are complaining items are not being sorted correctly." What users? A "user" who's writing reports? Users of a front-end application? "Users" developing said application? More detail here would inform me better to understand the full extent to which I need to fix the problem.

Now, let me play devil's advocate for a moment. I suspect what they're trying to convey here is that the change the fixes the problem AND affects the smallest amount of change on the database structure is the best practice. For instance, while changing the collation on the database and/or table level would indeed also fix the problem, who knows what else you might affect by doing this. I think the change made at the smallest unit is what they're pushing for.

I'd like someone else to chime in here as I'm sure there are (many) others more qualified than I to speak to this. I even think I may have seen replies from some of the MS E-Learning folks. Maybe, in a real world sceanrio, the other answers would be treated correctly. In standardized tests like these, the test is looking for the MOST correct answer. While the other 2 are correct, they may not be as correct as C.

Scott D. Jacobson SSCertifiable Points: 6089 More actions · Answer 3

Scott D. Jacobson

SSCertifiable

Points: 6089

August 24, 2012 at 11:15 am

#1529875

Nevermind, looks like Gail nailed it.

Lowell SSC Guru Points: 323504 More actions · Answer 4

well, A: cannot be correct at all: a table doesn't have collation, only the columns in a table do(and only those that happen to be varchar/char/nvarchar/nchar)

D cannot be correct, becuase if you change the database collation, the column is still at it's old collation, so the issue would not get fixed. the sorting problem would remain when you finished the ALTER DATABASE command.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Scott D. Jacobson (8/24/2012)
In standardized tests like these, the test is looking for the MOST correct answer. While the other 2 are correct, they may not be as correct as C.

Absolutely, and it's quite possible for the questions to have red herrings in (like the statement "regardless of the instance where the database is located") that have nothing to do with what the answer is. In the 2012 exams they're adding "Pick the best..." type questions and those are tricky because multiple of the answers are correct. Even with the older exams, it's not uncommon for there to be an answer that's partially correct and another that's completely correct.

That said, in this case the other two answers aren't even partially correct.

Now I'm not saying the practice exams are perfect, they're far from that. Even in the real exams I've found incorrect questions (that said, I write the exams during beta, so that's expected)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 6

GilaMonster (8/24/2012)
derek.colley (8/24/2012)
Which presumably means that if the database was located to an instance with a different collation setting, all database, table/object and column levels of collation would achieve this effect. So A, C and D are technically correct.
No they aren't.
Instance collation only affects the collation of the system databases and the default collation of any databases when they are created
Database collation only affects the default collation of columns when they are created.
Tables don't have a collation.
If I have a column with an incorrect collation (let's say it's somehow binary and it should be latin1_general) and hence sorts in a non-intuitive order changing the instance collation will have no effect whatsoever (well, it'll require downtime to implement, but the sort will be unaffected). Changing the database collation will have no effect because it's an existing column in an existing table. It'll ensure that any new tables created with string columns have correct collation, but won't affect the current tables.
The only way to fix an incorrect collation on a column of an existing table is to change the collation at a column level.
Well, I could also add a COLLATE clause to the order by of the query, that would fix it too, but that's not an option in the question.

An excellent riposte and demonstrates quite well my lack of knowledge in this area :-). To shift my rage onto another target (only a little!) it's a shame the explanation for this question isn't in as fine detail as what you're said above. The explanation, indeed, implies that table level collation IS possible. It doesn't help when the testee gets something wrong but doesn't get a full explanation of why that is the case.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.