June 27, 2012 at 9:12 am
Got it right because I know creating an FK doesn't create and index and you can't specify such a thing in the syntax. It has been odd lately that I've been getting these Jedi mind trick questions right and at times I get the simple ones wrong just because I didn't read is close enough.
Cheers
June 27, 2012 at 9:13 am
Thanks Hugo.
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
June 27, 2012 at 9:15 am
Toreador (6/27/2012)
tim.bearne (6/27/2012)
The fact that you can get an index made to support a completely different constraint is irrelevant.No it's not. Just because it is created for one purpose doesn't mean that it can't achieve a different purpose as well. An index created to support a unique constraint will also support the foreign key constraint, even though that's not why it was created.
But that wasn't the point of the question.
Cheers
June 27, 2012 at 9:17 am
jfogel (6/27/2012)
But that wasn't the point of the question.
I agree now I know the answer.
But it could have been - it was the point of the previous question in this series so why not this one too?
June 27, 2012 at 9:34 am
Toreador (6/27/2012)
jfogel (6/27/2012)
But that wasn't the point of the question.
I agree now I know the answer.
But it could have been - it was the point of the previous question in this series so why not this one too?
Mind games!
Cheers
June 27, 2012 at 9:49 am
Koen Verbeeck (6/27/2012)
vk-kirov (6/26/2012)
I can create an index by means of a unique constraint:
CREATE TABLE dbo.QotD(KeyColumn int NOT NULL PRIMARY KEY,
RefColumn int NOT NULL,
-- More column definitions
CONSTRAINT FK_RefTab FOREIGN KEY (RefColumn)
REFERENCES dbo.RefTab(RefTabKey),
-- More constraints
CONSTRAINT UQ_RefColumn UNIQUE(RefColumn)
);
Doesn't it count as answer #2? 🙂
It's possible, but I doubt a 1-1 foreign key relationship is very useful 😀
I also took answer number 2. Forgot that you cannot specify an index directly in a create table statement. D'oh!
I knew you couldn't create the index, but a 1-1 cardinality does occasionally exist, so a Uniqueness constraint specified to help the FK should be a valid way to get a Uniqueness index onto a table in the create statement.
June 27, 2012 at 9:52 am
Thomas Abraham (6/27/2012)
I too went for the #2 choice. I knew that the no index would be created automatically, but thought ..... same as the others. At least I have lots of company.Thanks for the question Hugo.
I've been burned too many times by "and the rest of the create statement is syntactically correct" followed by "well you could have this or that in the rest of that statement so you're wrong".
I hate Semantics, I also hate trying to read somebody's mind when they wrote the question.
June 27, 2012 at 9:55 am
jfogel (6/27/2012)
Toreador (6/27/2012)
tim.bearne (6/27/2012)
The fact that you can get an index made to support a completely different constraint is irrelevant.No it's not. Just because it is created for one purpose doesn't mean that it can't achieve a different purpose as well. An index created to support a unique constraint will also support the foreign key constraint, even though that's not why it was created.
But that wasn't the point of the question.
How do you know that for certain when you answer it? If #2 had been correct and the answer had stated that you could create a Uniqueness constraint and get a Unique Index created that would also support the foreign key, then a bunch of other folks would be crying foul.
Sorry Hugo, normally I enjoy your questions, and when I get them wrong, I still learn something. Knowing that the answer could be considered to be either #1 or #2 depending is just a little upsetting.
June 27, 2012 at 10:05 am
If you are asking me, my point is that I answered the question based on how it was put. It didn't ask anything other than what type of index (if any) would be added based on the syntax given. All I can say is that this time I saw through a question that could have easily tripped me up. It just didn't this time. Earlier this week there was a question where I completely agreed it was confusing and suggested a "explain your answer" area would be appropriate for it. Maybe for this one too?
Cheers
June 27, 2012 at 10:19 am
I wanted desperately to pick # 2 but I couldn't figure out how to add that index before the ). YES!
I liked the question.
June 27, 2012 at 10:28 am
mtassin (6/27/2012)
Thomas Abraham (6/27/2012)
I too went for the #2 choice. I knew that the no index would be created automatically, but thought ..... same as the others. At least I have lots of company.Thanks for the question Hugo.
I've been burned too many times by "and the rest of the create statement is syntactically correct" followed by "well you could have this or that in the rest of that statement so you're wrong".
I hate Semantics, I also hate trying to read somebody's mind when they wrote the question.
I am in agreement with statement above. By including Answer #2 as one of the possibilities, the question went from "does SQL create an index with a foreign key" to "guess whats in the code not shown."
When I get the answer wrong because I do not have that knowledge, then it is a learning opportunity for me. When I get a question wrong because it is a play on word, then I am just left disappointed.
It is a good questions, just an "opps" on the answer.
June 27, 2012 at 11:01 am
Seems I can only Ditto all the quotes on the second answer! Got me I guess
Thanks for the great question Hugo. Strange not to see any replies from you in the discussions though... 😉
June 27, 2012 at 4:11 pm
To be honest I thought the question was quite clear. It asked what index SQLSERVER created to support the foreign key. It did not ask what other constraints, which may create indexes on the same column, could YOU add to the rest of the create statement which would also by chance support the FK.
SQLServer does not create any indexes to support FK's, you must do it yourself, so the only possible answer was "None". I disagree with people talking about the question being a play on words or having to read Hugo's mind. It wasn't. I agree you needed to read the question carefully and think about it but heck, it's a question from Hugo so that's just a given 😀
Cheers
Roddy
June 27, 2012 at 4:31 pm
Sorry for my lack of replies. I am currently attending the TechEd Europe conference in Amsterdam, so I have very little time for other stuff.
I don't have time to address all individual responses (I have to get out of bed in about 5 hours, and I'm far from ready to get in it), so I'll just try to cover the general feedback with a few sentences.
First: Thanks to everyone who had kind words about this questions, or about me or my questions in general. Much appreciated!
Second: It was never my intention to make this a wordplay or mindguessing game. I tried to cover all the possible unintended interpretations in the question. But I have to admit that I did overlook one.
Having a UNIQUE constraint on a FOREIGN KEY column is rare - but not impossible. The index created for the unique constraint will also support the foreign key. That was not what I wanted to test, though; my goal for this question was to test if people know that just declaring a foreign key does not automatically create a supporting index.
If I had considered this possible interpretation in advance, I would probably have added a small selection of sample data or so, or I would just have added a note that there is nu unique constraint on the foreign key column.
My apologies to all who picked the wrong answer because of this oversight. And also my apologies to everyone who felt tricked or deceived by my question.
June 27, 2012 at 8:43 pm
All I have to say is: keep those questions coming. 😀
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 15 posts - 31 through 45 (of 59 total)
You must be logged in to reply to this topic. Login to reply