April 14, 2014 at 12:53 pm
Despite my issue with the back story on this question, I do really enjoy Andy's questions as they get the old grey matter working. I find that they quite often reflect the sort of support issues that I get. The ones where a client rings up and says 'I am getting an error with application blah' ... 'What's the error?' ... 'Oh something about blah blah, we didn't save it though'. Let the investigations begin:-D
I hope you carry on with these questions Andy.
April 14, 2014 at 1:11 pm
Chris, I'm not trying for absurb, though maybe it seems that way (or is that way) at times. I'm trying to write challenging questions about fairly common topics and what I'm experimenting with is challenging the quizzee to make (or test) some assumptions based on known but incomplete facts. Sometimes the way I do that isn't quite real world because there would see the error message, but the problem still challenges you to think "why the heck would that happen". Trying do that in a format that can be written in a hundred words or so and figured out in a minute or two isn't easy. I'd say more like writing a mystery than the weekly class quiz on material just taught.
Always appreciate feedback on the questions, and you can see notes I write - mostly for me - about the questions and lessons learned (or not) at http://sqlandy.com/tag/question-of-the-day/. Hope you'll give a few more a try.
April 14, 2014 at 1:15 pm
Eric, I'll have to work on a better way to pose the question than "I get to see the error message and you don't". Question From Error Message is a good pattern, but that doesn't mean the question needs to refer to it. As for the rest, I'm trying to add clues and false clues to see if you can focus on the core question and not be swayed by things that don't matter. The hard part, of course, is it's tough to know what matters!
I haven't been asked to write exam questions (yet!), but writing these has definitely given me a new appreciation for how easy it is to make what could be a straight forward question ambiguous or worse without trying/realizing.
April 14, 2014 at 1:18 pm
Micky, thanks for that. I'll work on better stories, not the easiest thing for me so far.
April 14, 2014 at 2:40 pm
Nice question Andy. Never thought of the databases table as not being a real table.
April 14, 2014 at 11:15 pm
Good Question .
April 15, 2014 at 6:23 am
Stewart "Arturius" Campbell (4/14/2014)
Good question, thanks Andyalmost tripped up, but remembered that databases is a system view, in the sys schema, not dbo.
The only way to reflect it in the dbo schema is with either a view or synonym, against which a foreign key cannot be created.
+1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
April 15, 2014 at 7:31 am
Andy Warren (4/14/2014)
Chris, I'm not trying for absurb, though maybe it seems that way (or is that way) at times. I'm trying to write challenging questions about fairly common topics and what I'm experimenting with is challenging the quizzee to make (or test) some assumptions based on known but incomplete facts...
I'm not saying you were trying for absurd, I think you've been trying to be too clever about it. I understand you wanted it to be challenging, but I've found I've had to try to reverse engineer what you were thinking and why you omitted specific information in order to even understand what it is you are trying to ask. Why I think the results have been almost absurd is that with the levels of assumptions made, extraneous information given, and multiple answers that are correct if others don't have the same assumptions as you then it becomes more a test of how much the person understands how you think as opposed to how well they can troubleshoot a problem.
April 15, 2014 at 7:36 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 15, 2014 at 9:58 am
Hi Andy,
Its good try. But I will not agree with your answer.
I will go for Query used to check the data is not correct.
Because there can be cases where [databases] table may contain other names which is not in [backuplist] table. In this case your check query will still return no rows but Alter statement to create foreign Key will fail because of conflict. Alter statement will work only if we add NOCHECk instead of CHECK.
Moreover I can create a table in the name of "databases" in MSDB. How do we know that you created a synonym for a system view.
--
Dineshbabu
Desire to learn new things..
April 15, 2014 at 10:36 am
We could create a DDL trigger or a nightly job that replicates sys.databases to a dbo.DatabaseList table. Also, we don't really need a dbo.BackupList table and foreign key just to setup this one-to-one relationship. Simply add an additional column on this dbo.DatabaseList table to indicate which set of databases should be included in backup. That way we completely avoid the possibility that the backup subset would be orphaned from the database set.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 15, 2014 at 11:54 am
Chris, I hear you, I'm just not sure of the alternative. To some degree it is me against you, puzzler and puzzle-ee, and if I'm not clever,then the question just isn't very interesting. Now that could be because I'm sourcing the question the wrong way, using interesting and obscure errors and lessons learned as the starting point, which is a lot different than thinking "how do I teach x lesson about foreign keys" or whatever. I guess also I feel like that this is what troubleshooting and debugging is - if it's easy it's not interesting, if it's not easy its because there is a nuance to the problem or our view of it that is making it hard (or it's just plain hard!).
I agree that too much noise, too much effort to weed through distractors becomes annoying and detracts from the lesson that might be learned. QOTD is about learning, not trying to win by making impossibly complex and misleading questions.
Let's think that the lesson is "can't create a foreign key on a synonym", how do you build a question to see if someone knows that? One way is to list objects and say which of the following can be source/target of a foreign key. Straight forward to be sure and I think a good portion of QODT should be along those lines - testing knowledge, providing a short lesson. If you know the lesson it's not even interesting though. How do I make it interesting to someone more experienced who could make better guesses even if they don't know? If you get a minute, try this. Take my question and cross out everything you don't like/think is excess. Is there enough left for a question (hopefully!) and what is the skill level required to answer it then, vs where it started?
I'm aware that I'm pushing the definition of QODT with these. Quite a few like it, quite a few are frustrated by it. If I can figure out how to do what I'm trying to do better then maybe the frustration goes down.
Hope you have time for another reply or two, there is interesting stuff here - at least for me trying to bend the universe a little.
April 15, 2014 at 1:11 pm
Dineshbabu (4/15/2014)
Hi Andy,Its good try. But I will not agree with your answer.
I will go for Query used to check the data is not correct.
Because there can be cases where [databases] table may contain other names which is not in [backuplist] table. In this case your check query will still return no rows but Alter statement to create foreign Key will fail because of conflict. Alter statement will work only if we add NOCHECk instead of CHECK.
Moreover I can create a table in the name of "databases" in MSDB. How do we know that you created a synonym for a system view.
The problem here is that the databases object in this case refers to sys.databases. sys.databases is a view and belongs to the sys schema and not dbo.
Every system has a sys.databases and that is what the question was referring to.
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
April 16, 2014 at 12:29 am
SQLRNNR (4/15/2014)
Dineshbabu (4/15/2014)
Hi Andy,Its good try. But I will not agree with your answer.
I will go for Query used to check the data is not correct.
Because there can be cases where [databases] table may contain other names which is not in [backuplist] table. In this case your check query will still return no rows but Alter statement to create foreign Key will fail because of conflict. Alter statement will work only if we add NOCHECk instead of CHECK.
Moreover I can create a table in the name of "databases" in MSDB. How do we know that you created a synonym for a system view.
The problem here is that the databases object in this case refers to sys.databases. sys.databases is a view and belongs to the sys schema and not dbo.
Every system has a sys.databases and that is what the question was referring to.
Jason,
I Hope every sql server developer knows Sys.databases is system view and belongs to SYS schema. But my argument is that mentioned in the QotD. As well as in the hint it was given that all objects refferred here belongs to dbo Schema.
So my point is Alter statement will fail either if it is not a table or as I mentioned in my earlier post if target table contains more records than backuplist table.
--
Dineshbabu
Desire to learn new things..
April 16, 2014 at 2:25 am
SQLRNNR (4/15/2014)
Every system has a sys.databases and that is what the question was referring to.
No it isn't - the question clearly refers to [dbo].[databases]
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply