Hugo Kornelis (6/27/2012)
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.
apoliges accepted, I answered #2 because of the unique index possibility. It might be rare to use a 1-1 relation, but sometimes you want to partition your table vertical due to the amount of columns and how you use of them. Personally I have divided a wide table with many columns into two because of performance. The initial database design was bad, I know, but I was not responsible for that and I had to do something very quick to improve the performance. Part of the table was used frequently, but the other part was used only for some rare cases.
Instead of having 1 table with 500 Gb of data, I got 2 with 250 Gb each.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform