June 14, 2012 at 9:34 am
There are also a few people who didn't consider the possibility of unique constraints being defined in the same CREATE TABLE statement, for instance because they have the habit of using explicit ALTER TABLE statements for that. I just hope that these people don't feel tricked. I did not want to make this a trick question. I even explicitly added a comment "-- More column and constraint definitions follow" (emphasis added here), to make it extra clear that the reader should assume that the create table statement can containt more constraints than just the primary key. If anyone knows how I could have made this even more clear, do let me know; I always try to learn and improve my question writing skills.
Quite the opposite imho. I was actually thinking to myself several times during the initial reading of the question how much effort you put into the details and explanation to make sure this was NOT a trick question. I think the question and the very careful wording removed any ambiguity or "trickery".
Now if you could add some wording to make me think before I answer that would be great. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 9:41 am
venoym (6/14/2012)Then we are assuming that the author didn't intend to test us the default of the Primary Key?
From the description, emphasis mine. If you want a technicality, both CLUSTERED and the "Depends" answers are correct given the same information. This question was not testing the myth of Always Clustered on Primary Key, it was testing whether or not we think that Hugo would put a "Depends" answer or if he would go with what BOL calls the Default for a PRIMARY KEY statement.
I saw your post after I posted my previous message (which I started to write before your post was here), so I'll just add a few words.
1. I wanted to test your understanding of the rules for what index is created by default for a primary key. Those rules are a bit complex.
2. Yes, I want a technicality. As you yourself just quoted from Books Online, the documentation clearly states that the default index type is clustered in one cases and nonclustered in another case. That makes "depends" the ONLY correct answer.
It is of course possible that you did know about the exception to the "clustered" rule but was not sure if I was aware of it as well. I understand that - I have been in similar situations before, and I've made the wrong assumption before. In that case, I am sorry for you. And, again, I request your help in improving my question-writing skills. I have deliberately not worded the correct answer as simply "it depends", but worded it very specific - I did this in the hope that this would convince everyone who knows about the exception that I know about it too. Apparently, I did not succees in achieving this goal. Do you have any suggestions as to how I could have improved this question?
June 14, 2012 at 9:48 am
venoym (6/14/2012)
ronmoses (6/14/2012)
L' Eomot Inversé (6/14/2012)
Most of those errors were the same wrong answer, there appears to a widely distributed myth that any primary index is clustered unless it is declared as unclustered. I knew that such a myth existed, but I thought it was so often debunked that only a small proportion of people still believed it.I don't think I'd call it a myth so much as an incomplete understanding of index creation pertaining to primary keys. Since the default is, in fact, a clustered index, many may never have considered the possibility that there was any other option. I know I hadn't. But I don't think I would describe my situation as tantamount to having mindlessly bought into a long-debunked ghost story that of course nobody in the modern age believes anymore.
ron
If I may. I answered Clustered. The reason is that I was trying to divine the intent of the author. Is the intent to test your Index DEFAULT (as the name of the QotD implies), in which case the answer CLUSTERED is correct. The Default for PRIMARY KEY is CLUSTERED. In this case the Intent was to see if you knew the second half, which is... if CLUSTERED explicitly listed later in the Table Definition, does the PRIMARY KEY become CLUSTERED or NONCLUSTERED?
I submit that 86% are answering question 1, while the QotD is on Question 2. If it is the intent to test Question 2, then the question is poorly worded.
Well said. Ditto.
Rob Schripsema
Propack, Inc.
June 14, 2012 at 10:42 am
Victor Kirkpatrick (6/14/2012)
Awesome question. Instinct was to pull the trigger on clustered, but then I made sure to read all the answers. So I tried to create the table and also add a column with a unique clustered constraint on it, and viola, the KeyColumn index changed to a nonclustered.Learned something here, which is the point.
Ditto for me...almost chose "clustered" then something made me stop and read the question again carefully. After reading the question carefully, I wasn't sure that my 'gut' feeling was correct and I quickly tested it to find that my 'gut' was wrong. Although I understand the objections raised, if you read the question carefully, it is not ambiguous nor is it a trick question. But it does require both careful reading of the question and thinking about what it actually says. After all is said and done, this has to be one of my favorite questions of the day!
June 14, 2012 at 10:46 am
Hugo Kornelis (6/14/2012)
venoym (6/14/2012)Then we are assuming that the author didn't intend to test us the default of the Primary Key?
From the description, emphasis mine. If you want a technicality, both CLUSTERED and the "Depends" answers are correct given the same information. This question was not testing the myth of Always Clustered on Primary Key, it was testing whether or not we think that Hugo would put a "Depends" answer or if he would go with what BOL calls the Default for a PRIMARY KEY statement.
I saw your post after I posted my previous message (which I started to write before your post was here), so I'll just add a few words.
1. I wanted to test your understanding of the rules for what index is created by default for a primary key. Those rules are a bit complex.
2. Yes, I want a technicality. As you yourself just quoted from Books Online, the documentation clearly states that the default index type is clustered in one cases and nonclustered in another case. That makes "depends" the ONLY correct answer.
It is of course possible that you did know about the exception to the "clustered" rule but was not sure if I was aware of it as well. I understand that - I have been in similar situations before, and I've made the wrong assumption before. In that case, I am sorry for you. And, again, I request your help in improving my question-writing skills. I have deliberately not worded the correct answer as simply "it depends", but worded it very specific - I did this in the hope that this would convince everyone who knows about the exception that I know about it too. Apparently, I did not succees in achieving this goal. Do you have any suggestions as to how I could have improved this question?
Thank you for your thoughtful replies Hugo. It is very difficult to have a clear, unambiguous, and "good" QotD... I've submitted one myself on Service Broker and Transactions that Koen and others have graciously helped me with when there were questions about it.
I guess the thing that got to me, and why I've taken time to respond the times I have, is that when viewing a Table definition a programmer or DBA will have more than simply the Primary Key. Given that no other columns are specified as UNIQUE CLUSTERED (the only condition for PRIMARY KEY to result in a NONCLUSTERED index), it seems that the definition in BOL gives a 2 part answer. The first part is the majority case of default CLUSTERED, like saying that "There is no Nudity on Network TV". The second part details the exception to the rule, in the example I just mentioned it would be "Except after 2 AM and before 5 AM" or "Except for 'side' shots of buttocks after 9 PM".
All of that above being said, The only thing I might be able to recommend is to add or alter the line to mention the possibility of a UNIQUE CLUSTERED index in the rest of the table creation statement. Given that it is the documented exception to the rule (in my opinion) instead of the whole rule.
Again Hugo, I really appreciate the question and it has really made me think and learn in the process so I count it as a very good question. Doing QotD is tougher than people realize and I say Hats off to you for putting yourself through it!
June 14, 2012 at 12:05 pm
venoym (6/14/2012)
All of that above being said, The only thing I might be able to recommend is to add or alter the line to mention the possibility of a UNIQUE CLUSTERED index in the rest of the table creation statement. Given that it is the documented exception to the rule (in my opinion) instead of the whole rule.
Thank you for your thoughtful reply. You are absolutely right that explicitly mentioning a unique constraint with the clustered keyword would have eliminated the final bit of possible misunderstanding. On the other hand, I also feel that explicitly mentioning that possibility would give away too much of the answer. Sometimes, it's all about inding the right balance. It may well be the case that we have to agree to disagree on where the right balance is in this case.
Again, thanks for your comments!
June 14, 2012 at 12:48 pm
Hugo Kornelis (6/14/2012)
venoym (6/14/2012)
All of that above being said, The only thing I might be able to recommend is to add or alter the line to mention the possibility of a UNIQUE CLUSTERED index in the rest of the table creation statement. Given that it is the documented exception to the rule (in my opinion) instead of the whole rule.Thank you for your thoughtful reply. You are absolutely right that explicitly mentioning a unique constraint with the clustered keyword would have eliminated the final bit of possible misunderstanding. On the other hand, I also feel that explicitly mentioning that possibility would give away too much of the answer. Sometimes, it's all about inding the right balance. It may well be the case that we have to agree to disagree on where the right balance is in this case.
Again, thanks for your comments!
I was thinking again (as my wife says, that's not good)... Instead of changing the question, perhaps adding the word "only" to the "CLUSTERED" answer. That would remove it from even a cursory examination of the answers.
In any event, I hope you continue providing questions for QotD!
June 14, 2012 at 1:08 pm
Hi Hugo,
Hugo Kornelis (6/14/2012)
I even explicitly added a comment "-- More column and constraint definitions follow"
That's what steered my away from the obvious answer (clustered) and toward Books Online.
Thanks for the great question.
June 14, 2012 at 1:28 pm
Hugo Kornelis (6/14/2012)
There are also a few people who didn't consider the possibility of unique constraints being defined in the same CREATE TABLE statement, for instance because they have the habit of using explicit ALTER TABLE statements for that. I just hope that these people don't feel tricked. I did not want to make this a trick question. I even explicitly added a comment "-- More column and constraint definitions follow" (emphasis added here), to make it extra clear that the reader should assume that the create table statement can containt more constraints than just the primary key. If anyone knows how I could have made this even more clear, do let me know; I always try to learn and improve my question writing skills.
I have to admit I originally felt tricked, but read all the the other comments before I added mine, so I didn't feel tricked anymore. I answered too quickly because I "knew" the answer. If I had really thought about it I would have gotten it right, but I went with the quick answer. Again because my practice is to add constraints separately from the CREATE TABLE.
As I thought about it more it actually is a good question, especially because the opportunity to learn something from it. IF you are answering the QoD for the points and not for the knowledge, I think, you are doing it for the wrong reason.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 14, 2012 at 2:55 pm
Great question Hugo. I do typically create constraints using the ALTER TABLE statement and I got it wrong, but I did not feel tricked at all. I enjoyed the exploration of creating multiple constraints within the CREATE TABLE statement itself.
Ken Garrett
June 14, 2012 at 3:58 pm
Trick questions are the reason I rearely bother with QOD. It woudl be great to learn things but when there is so little information and only a mention of other constraints being added not indexes of course the SQL default behaviour is expected. If the questions had said other constraints AND indexes were being added then it was a no brained "it depends". So I think is was a badley worded question that was ensuring a high failure rate.
Editied to remove stroppy and inappropriate comments.
June 14, 2012 at 4:49 pm
dogramone (6/14/2012)
If the questions had said other constraints AND indexes were being added then it was a no brained "it depends".
It is not possible to add indexes in a CREATE TABLE statement, except those that are implicitly created for PRIMARY KEY and UNIQUE constraints. If the question had said "other constraints and indexes", it would have been incorrect.
I would not use this question in a job interview (though I might ask the candidate a more open question, e.g. "what kind of index will be created to support a PRIMARY KEY constraint", and then engage in dialog until I know whether he does or doesn't know this - and if he doesn't, that would not be a reason by itself to reject him). I might use it if I ever were to make an exam to assess a person's SQL Server skills. I believe that a good exam has a good mix of questions at different difficulty levels, to make sure that the result is a good measure of the candidate's competence. This question would be one of the hardest, that I expect only the best to get right.
I am sorry that you felt tricked by this question. I tried hard to avoid that, and I sincerely apologise if you feel that I failed.
I'll disregard the rest of your message, because it's bordering on ad-hom and I don't want to get involved in a flame war.
June 14, 2012 at 5:17 pm
Hugo, Sorry if you thought I was trying to enter into a flame war, that was not the intent.
To all others that have read my previous post and feel I was laying it on thick against Hugo, that was not my intent. I have been a member here for about a decade and have read many posts and articles written by Hugo and have learnt a lot of very valuble information from him. I see Hugo as one of the "heros" on this site along with Jeff, Gail, Lyn, Paul to name but a few. I highly respect Hugo's knowledge and capabilities and would be willing to blindly follow any suggestion or recommendation that Hugo makes as I know he has knowledge and experience to make best practices recommendations.
June 14, 2012 at 5:32 pm
dogramone (6/14/2012)
Hugo, Sorry if you thought I was trying to enter into a flame war, that was not the intent.To all others that have read my previous post and feel I was laying it on thick against Hugo, that was not my intent. I have been a member here for about a decade and have read many posts and articles written by Hugo and have learnt a lot of very valuble information from him. I see Hugo as one of the "heros" on this site along with Jeff, Gail, Lyn, Paul to name but a few. I highly respect Hugo's knowledge and capabilities and would be willing to blindly follow any suggestion or recommendation that Hugo makes as I know he has knowledge and experience to make best practices recommendations.
Thanks for clarifying that you didn't intend your message to be a personal attack. And thanks for the kind words about me (and many others).
The only thing I want to add to this, is to please NEVER follow my suggestions blindly (or anyone's suggestions). Everyone is human, everyone makes mistakes. Always check on all advise you get. 'kay? 😉
June 14, 2012 at 6:36 pm
Trick question is not quite the right description of this.
I was one of the "myth" believers and now I've learned something. Whilst it might have been a bit painful getting it wrong and in retrospect a bit obvious if I had read it more carefully, it was certainly effective. Learning from mistakes is what life is all about.
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply