December 7, 2012 at 5:13 am
Richard Warr (12/7/2012)
Bit worrying how many people found this easy without giving any consideration to what the default schema might be for the user.
How do you know whether they considered it?
If it had been either of the two named schemas then the answer would have ben Success Success Fail, but that wasn't an option, so the default had to be something else, probably dbo.
December 7, 2012 at 5:37 am
Interesting question, but needs a bit more statement of conditions to make it a perfect question. It needs to specify that the table doesn't already exist in the the beneficiary schema.
Of course I firmly believe that anyone who got a wrong answer because the started worrying about the possibility that a table with that name already existed in the beneficiary schema is pretty far gone, in fact has probably reached the distant end of the path that "call.copse" so appropriately deprecated! In reality, as call.copse effectively said, it's a straightforwards question with no real issues.
Some people have claimed that a problem with this question is that it doesn't specify permissions, but I don't agree. Surely it's fair to say that "I have two schemas A and B" implies they are my schemas, so "I have permission to put things into schemas A and B", since it's difficult to see much sense in the idea that someone "has" a schema that he doesn't have permission to use to put things in. It also doesn't need to specify that the users default schema is neither of the two named schemas, because the result success/success/fail (the only one that could be caused by not having that constraint, in the absence of one of the tables pre-existing) isn't one of the allowed answers.
But all three answers are possible even if we assume all permissions needed are there. A statement will fail if it's trying to create a table that already exists, so if the table already exists in schema beneficiary , not in schema person, not in the users default schema, and the users default schema is not person the second option is correct; while if the schema exists only in schema beneficiary, which is the users default schema, or the table exists in both schema beneficiary and in the users default schema the third option will be correct. And of course if neither person nor beneficiary is the users default schema and the table does not exist in either of those schemas or in the users default schema we get the first option.
So the fault of this question isn't about permissions. Nor is it about the user's default schema, but about preexisting tables, since the only result that could be caused by something strange about the users default schema without also having a preexisting table is not one of the three outcomes offered, and it's a general property of multiple choice questions that conditions that would lead to a result that is not one of the answers offered can be ruled out by simple logic.
Tom
December 7, 2012 at 5:44 am
Easy question, but explanation is a bit lacking.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 7, 2012 at 6:49 am
I knew this question would generate some discussion as soon as I read "Suppose I have two schemas: "
For reference, a database created with default options in SQL Server 2008R2 Express has 13 schemas to begin with:
dbo
guest
INFORMATION_SCHEMA
sys
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter
Now you can drop all of those but dbo, guest, INFORMATION_SCHEMA, and sys -- these four are system-required.
So when you said "Suppose I have two schemas", what you meant was "Suppose I have two user-defined schemas, or at least six in total."
😛
December 7, 2012 at 7:01 am
Koen Verbeeck (12/7/2012)
Easy question, but explanation is a bit lacking.
I agree, of course the discussion afterwards generally gives a better explanation and interesting conversation anyway 🙂
December 7, 2012 at 7:16 am
"Suppose I have two schemas" does lead the reader to consider that the user only has access to those two schemas. I initially was looking for Success, Success, Fail, but since that wasn't an option...
"Grammer is hard"
Aigle de Guerre!
December 7, 2012 at 7:28 am
I would have specified that the default schema is 'dbo' just to be clear, but I suppose without an indication of the default schema you would have to assume the default.
December 7, 2012 at 7:37 am
Got it wrong :w00t:. Should have gone with my first instinct, but then started to read into it. Should have assumed that the permissions were dbo.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
December 7, 2012 at 9:06 am
It's probably good that Success.Success.Fail was not listed as an option since at this point, 89% have answered correctly. With the other answer as a choice, I am sure the number of "correct" answers would be down around 40-50% and there would be 10 pages of ranting. A good question in concept, it just needed a little more detail as to the user's schema permissions/defaults.
December 7, 2012 at 9:57 am
Yes. You are right. This question is not about schema permissions, it’s only about uniqueness of schema. Under normal settings dbo is the default schema. From next time onwards when am submitting the QOTD I will give proper explanation.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
December 7, 2012 at 9:59 am
call.copse (12/7/2012)
I thought the question was clearly not about rights, but uniqueness in schemas, and thus not ambiguous, of course it might have been helpful to indicate rights were available. Though you might need to add that the network does not drop, disk space is still available, aliens don't squash the server etc if you are pushing it that far.
Yes. You are right. This question is not about schema permissions, it’s only about uniqueness of schema. Under normal settings dbo is the default schema. From next time onwards when am submitting the QOTD I will give proper explanation.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
December 7, 2012 at 11:08 am
Stuart Davies (12/7/2012)
Assumed I had rights to person and beneficiary schemas and got it right.Otherwise the answer could be it depends,it depends,it depends
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2012 at 12:21 pm
The 'testibility' of this quiz question is not very good ;-)– even I know nothing about database whatsoever I’ll still be able to get the correct answer – because the first 2 are identical in nature, so they either both ‘success’ or both ‘fail’ – therefore #1 became the only possibility to be right. FAIL FAIL SUCCESS should be an option to really test the subject.:-P
December 7, 2012 at 1:39 pm
It was so simple that I thought, for a moment, that it was a trick question.
Thanks for making my Friday easy!
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply