Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

Index defaults 2 Expand / Collapse
Author
Message
Posted Thursday, June 28, 2012 2:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1322751
Posted Sunday, July 1, 2012 8:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 5,366, Visits: 8,983
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1323615
Posted Tuesday, July 3, 2012 10:04 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1324768
Posted Thursday, July 12, 2012 3:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 19, 2012 12:50 PM
Points: 385, Visits: 170
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
Post #1329209
Posted Wednesday, July 18, 2012 12:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 7,855, Visits: 9,603
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
Post #1331760
Posted Wednesday, July 18, 2012 12:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 7,855, Visits: 9,603
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
Post #1331766
Posted Wednesday, July 18, 2012 1:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 7,855, Visits: 9,603
misguided post erased

Tom
Post #1331774
Posted Tuesday, July 31, 2012 6:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 2:56 AM
Points: 2,624, Visits: 581
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
Post #1337842
Posted Wednesday, August 8, 2012 12:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55
Learned something.
Post #1342106
Posted Sunday, May 12, 2013 12:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:11 PM
Points: 1,940, Visits: 1,171
Even though i know the answer i got it wrong . I got confused with question

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1451919
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse