InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 02:21:21 GMT20http://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxEven though i know the answer i got it wrong . I got confused with questionSun, 12 May 2013 12:14:52 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxLearned something.Wed, 08 Aug 2012 12:11:02 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Hugo Kornelis (6/27/2012)[/b][hr]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.[/quote]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.Tue, 31 Jul 2012 06:23:19 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxmisguided post erasedWed, 18 Jul 2012 13:04:10 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Toreador (6/27/2012)[/b][hr][quote][b]vk-kirov (6/26/2012)[/b][hr]I can create an index by means of a unique constraint[/quote]That'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![/quote]In what sense does that index support the foreign key constraint? The question asks specifically about creation of an index [b]to support the foreign key constraint[/b].Wed, 18 Jul 2012 12:53:28 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Toby Harman (6/26/2012)[/b][hr]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.[/quote]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.Wed, 18 Jul 2012 12:48:02 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxHi 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 GarrettThu, 12 Jul 2012 15:48:03 GMTKen Garretthttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Hugo Kornelis (6/27/2012)[/b][hr]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.[/quote]Thanks :-)Tue, 03 Jul 2012 22:04:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxIMO, 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.Sun, 01 Jul 2012 20:56:04 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]roger.plowman (6/28/2012)[/b][hr]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?[/quote]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.Thu, 28 Jun 2012 14:47:56 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxWhile 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...Thu, 28 Jun 2012 07:11:27 GMTroger.plowmanhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxgot something new !!!Thu, 28 Jun 2012 07:08:56 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxOh my.. that's what happens if you do Reply instead of Edit, after a 15 hour workday...Sorry. :ermm:Wed, 27 Jun 2012 22:14:05 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Revenant (6/27/2012)[/b][hr][quote][b]Hugo Kornelis (6/27/2012)[/b][hr]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.[/quote]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 [i]de Wallen[/i] for me, if you will.[/quote]Wed, 27 Jun 2012 22:12:35 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Hugo Kornelis (6/27/2012)[/b][hr]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.[/quote]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 [i]de Wallen[/i] for me, if you will.Wed, 27 Jun 2012 22:11:18 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxAll I have to say is: keep those questions coming. :-DWed, 27 Jun 2012 20:43:13 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxSorry 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.Wed, 27 Jun 2012 16:31:26 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxTo 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 :-D CheersRoddyWed, 27 Jun 2012 16:11:57 GMTRoddy.CAMERONhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxSeems I can only Ditto all the quotes on the second answer! Got me I guessThanks for the great question Hugo. Strange not to see any replies from you in the discussions though... ;-)Wed, 27 Jun 2012 11:01:31 GMTMichael Riemerhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]mtassin (6/27/2012)[/b][hr][quote][b]Thomas Abraham (6/27/2012)[/b][hr]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.[/quote]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.[/quote]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.Wed, 27 Jun 2012 10:28:14 GMTMike Hayshttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxI wanted desperately to pick # 2 but I couldn't figure out how to add that index before the ). YES!I liked the question.Wed, 27 Jun 2012 10:19:41 GMTJohn Hanrahanhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxIf 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?Wed, 27 Jun 2012 10:05:47 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]jfogel (6/27/2012)[/b][hr][quote][b]Toreador (6/27/2012)[/b][hr][quote][b]tim.bearne (6/27/2012)[/b][hr]The fact that you can get an index made to support a completely different constraint is irrelevant.[/quote]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.[/quote]But that wasn't the point of the question.[/quote]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.Wed, 27 Jun 2012 09:55:14 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Thomas Abraham (6/27/2012)[/b][hr]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.[/quote]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.Wed, 27 Jun 2012 09:52:05 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Koen Verbeeck (6/27/2012)[/b][hr][quote][b]vk-kirov (6/26/2012)[/b][hr]I can create an index by means of a unique constraint:[code="sql"]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 [b]CONSTRAINT UQ_RefColumn UNIQUE(RefColumn)[/b] );[/code]Doesn't it count as answer #2? :-)[/quote]It's possible, but I doubt a 1-1 foreign key relationship is very useful :-DI also took answer number 2. Forgot that you cannot specify an index directly in a create table statement. D'oh![/quote]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.Wed, 27 Jun 2012 09:49:08 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Toreador (6/27/2012)[/b][hr][quote][b]jfogel (6/27/2012)[/b][hr][quote][b]But that wasn't the point of the question.[/quote]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?[/quote]Mind games!Wed, 27 Jun 2012 09:34:34 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]jfogel (6/27/2012)[/b][hr][quote][b]But that wasn't the point of the question.[/quote]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?Wed, 27 Jun 2012 09:17:50 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Toreador (6/27/2012)[/b][hr][quote][b]tim.bearne (6/27/2012)[/b][hr]The fact that you can get an index made to support a completely different constraint is irrelevant.[/quote]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.[/quote]But that wasn't the point of the question.Wed, 27 Jun 2012 09:15:04 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxThanks Hugo.Wed, 27 Jun 2012 09:13:45 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxGot 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.Wed, 27 Jun 2012 09:12:48 GMTjfogelhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Toby Harman (6/26/2012)[/b][hr]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.[/quote]Ditto to that!Wed, 27 Jun 2012 09:10:52 GMTseth delcontehttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx+1 for answer #2... FK won't create an index, but I got sucked into the trap overthinking the OTHER CONSTRAINTS.Thanks for the question Hugo - cheersWed, 27 Jun 2012 09:05:13 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxThanks for the question Hugo.Wed, 27 Jun 2012 09:00:30 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxGreat question that really made me think. Probably should have added another assumption:5. RefColumn is not unique in table dbo.QotD.Wed, 27 Jun 2012 08:32:33 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxThank you for the question, Hugo. This is the kind of question that makes you think over and over again."El" Jerry.Wed, 27 Jun 2012 08:24:56 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxOh... I see how it is. :rolleyes:We put weird and misleading wording in answers #1 & #2 so people get it wrong.Wed, 27 Jun 2012 07:58:41 GMTryan.mcateehttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]tim.bearne (6/27/2012)[/b][hr]The fact that you can get an index made to support a completely different constraint is irrelevant.[/quote]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.Wed, 27 Jun 2012 07:35:48 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxQuestion was "what type of index (if any) will SQL Server make to support the FOREIGN KEY constraint?". Answer is "none" and it's not possible to specify one in the rest of the statement. The fact that you can get an index made to support a completely different constraint is irrelevant.Wed, 27 Jun 2012 07:25:45 GMTtim.bearnehttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspx[quote][b]Duncan Pryde (6/27/2012)[/b][hr]I think the question was designed partly to check people didn't think that creating a foreign key also created an index - which is not an uncommon misconception.[/quote]Exactly my thoughts.Even after getting the answer right I still have learned something new.Excellent discussion and QotD!Wed, 27 Jun 2012 07:05:09 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1321645-1328-1.aspxI went for answer #2 as well, but then realized after the fact that the create index is indeed a seperate statement. So I learned a couple new things already today, the second being watch out for these close answers! :-)Wed, 27 Jun 2012 06:28:15 GMTDan Graveen