Index defaults 2

  • 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.

  • 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.

  • Oh my.. that's what happens if you do Reply instead of Edit, after a 15 hour workday...

    Sorry. :ermm:

  • got something new !!!

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • 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...

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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 πŸ™‚

  • 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

  • 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

  • Toreador (6/27/2012)


    vk-kirov (6/26/2012)


    I can create an index by means of a unique constraint

    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!

    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

  • misguided post erased

    Tom

  • 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

  • Learned something.

  • 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