June 27, 2012 at 10:11 pm
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.
Hugo, this is how the world works today: no matter what you do, there is always somebody against it.
Please keep them coming, no matter whether I score a point or not. I always learned someting valuable from your posts, correct answer or not.
IMO, that's what counts.
Pl;ease say HI! to de Wallen for me, if you will.
June 27, 2012 at 10:12 pm
Revenant (6/27/2012)
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.
Hugo, this is how the world works today: no matter what you do, there is always somebody against it.
Please keep them coming, no matter whether I score a point or not. I always learned someting valuable from your posts. IMO, that's what counts.
Please say Hi! to de Wallen for me, if you will.
June 27, 2012 at 10:14 pm
Oh my.. that's what happens if you do Reply instead of Edit, after a 15 hour workday...
Sorry. :ermm:
June 28, 2012 at 7:08 am
got something new !!!
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
June 28, 2012 at 7:11 am
While I'm late to the party and missed answering yesterday 🙂 I knew foreign keys don't generate indexes, but my question is why the heck not?
Foreign keys are there because you're going to join the data--and efficient joins require indexes. Therefore under what conditions would you *not* want a foreign key to be indexed? Shouldn't SQL Server's create statement index by default using a similar naming convention that it uses for other default names?
Or failing that, at least have an index option on the foreign key creation code? On by default, but allowing it to be overridden in the rare case you really don't want an index?
The more I work with SQL Server the more I get the feeling it's intended as a "manual transmission", it does NOTHING unless you explicitly tell it.
Me, I prefer an automatic that knows to do a few things itself...
June 28, 2012 at 2:47 pm
roger.plowman (6/28/2012)
While I'm late to the party and missed answering yesterday 🙂 I knew foreign keys don't generate indexes, but my question is why the heck not?
Maybe because they often bring more harm than good?
A very common (not the only!) scenario for a foreign key constraint is a large table with data that has foreign keys into several other, smaller ("lookup") tables. Let's look at a few scenarios.
1. A query that joins the tables - in most cases, the large table will "drive" the query. Ideally through a seek that uses a filter to limit the number of rows read from the large table. The data from the smaller data will then be added by a seek on the primary key of that table. The index on the large table will not be used in this case.
You may also see a plan that starts to read the smaller table and then joins to the large table. If that plan uses a lookup, an index on the foreign key column will definitely be beneficial for a loop join or merge join; less so for a hash join.
2. Modification in the large table are often very frequent in this scenario. For inserts and updates, the existence of the value in the small table has to be checked; this uses the index on the smaller table's primary key. The index on the foreign key column will not be used, but has to be updated for every insert, update, and delete.
3. Modifications to the small table are far less frequent. For inserts and updates that don't affect the primary key, no check against the large table is required. For deletes, the alrge table has to be checked to verify that the value is not used. This check will benefit from an index on the foreign key.
So - for querying, the most usual query pattern may in the majority of cases not use the foreign key index at all. For modifications, the most common modification will be slowed down by the index on the foreign key column, and only a less frequent modification will benefit.
Please don't take the above to mean that I think you should never have an index on a foreign key column. In many cases, you should. But not in all. And that's why I think it's a good thing that SQL Server does not add them automatically.
July 1, 2012 at 8:56 pm
IMO, another valid answer to this question is "Clustered OR nonclustered index, depending on the specification of other constraints". Tying in with the building a unique constraint on the FK column, then it would be as defined. I can also see how this could be interpreted as it would build an index, regardless of other constraints, and in that case it would be wrong.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 3, 2012 at 10:04 pm
Hugo Kornelis (6/27/2012)
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 [...] 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.
Thanks 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 12, 2012 at 3:48 pm
Hi Hugo,
My apologies for posting so late in the game.
Excellent question. I did get it wrong by overthinking it and delving into the use of a CLUSTERED or NONCLUSTERED UNIQUE table constraint on RefCol. Still a great question, but I should have went with my first instinct which was 'None'.
Ken Garrett
July 18, 2012 at 12:48 pm
Toby Harman (6/26/2012)
You have two answers that are potentially correct here.None, and None unless the rest of the command specifies one.
Shame I picked the wrong one!
Edit: I stand corrected. The CREATE INDEX has to be a separate command. Missed that.
I was tempted to go down the "...unless..." path because I can envisage the rest of the create statement introducing an UNIQUE constraint on the foreign key column - that produces an index to support the UNIQUE constraint, and as the foreign key column is the only column in the unique key it can be used as an index on that column. However, I didn't go there because an index on the referencing column does nothing to support the Foreign Key constraint - support for a Foreign Key constraint is provided by an index (created with a Primary Key or Unique constraint) on the referenced key in the referenced table, and indexes in the referencing table don't provide any such support. Actually they do - they support updates and deletes of referenced keys quite nicely if you have such horrors - but SQL Server won't generate an index with teh purpose of providing such support because such an index more often degrades performance than improves it; the question did appear to me to be very clearly about creating an index with the puropose of supporting the foreign key constraint.
Tom
July 18, 2012 at 12:53 pm
Toreador (6/27/2012)
vk-kirov (6/26/2012)
I can create an index by means of a unique constraintThat's exactly the basis on which I answered "None unless the rest of the statement creates one", which I therefore believe should be the correct answer. Particularly given that the previous "index defaults" question was about the possibility of a Unique constraint appearing later in the statement!
In what sense does that index support the foreign key constraint?
The question asks specifically about creation of an index to support the foreign key constraint.
Tom
July 18, 2012 at 1:04 pm
misguided post erased
Tom
July 31, 2012 at 6:23 am
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
August 8, 2012 at 12:11 pm
Learned something.
May 12, 2013 at 12:14 pm
Even though i know the answer i got it wrong . I got confused with question
Malleswarareddy
I.T.Analyst
MCITP(70-451)
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply