An Un-indexed Foreign Key Gotcha

  • Good Article with useful info. Thanks

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The indexed FKs have nothing to do with performance; increased performance MIGHT be a side-effect, but - as you point out - it might as well not be such an effect. The idea of indexing FK columns has to do with the locking mechanisms.

    In order to be sure that you can delete a parent record you must also make sure that there are no children belonging to that parent. This can only be achieved by actually reading the child table(s). When you don't have any usable index on the child table, then you must read all records - and they get locked at the same time, just because they are part of an updating (read-write) transaction.

    This is very common truth fΓΆr many relational databases, including SQL Server, Oracle, Firebird and others, and it has so been since "ancient" times.

    P.S. I thought it was common knowledge... πŸ˜‰

  • The indexed FKs have nothing to do with performance;

    I think that is a bit of a stretch. There is almost always at least some performance benefit (often dramatic), both in efficient join/seek and in improved concurrency from reduced locking.

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Please read Thomas Kyte's books. He wrote for Oracle but the principles about "transaction processing" and "locking" and "index" are the same.

    When FK records can be fastly located by index, the transaction processing will be fast, that reduces required lock-time, hence improves performance. This applies to any records.


  • I agree it's a bit of a stretch. πŸ™‚

    My point was that indexing might have different purposes, optimizing search operations being just ONE of them.

  • sqlpro (11/9/2009)

    thanks for the reply. probably i misunderstood but following is the link i got my understanding from.its here

    Careful. You are quoting from a book that is over ten years old. This is usually fine if the book is about theoretical or academic work but that book is a "Teach Yourself To Be A Dummy In 60 Seconds" styled book on SQL Server 7.

    I know some people may still be supporting SQL Server 7 installations.

    I know that a lot of people like these types of books.

    I only said be careful.


    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • A really nice article. Good explanation.



  • Enforcing delete cascade will also help here, instead of creating indexes. I believe it should eliminate the deadlock. Of course if you enable delete cascade it's no point in deleting from child table first and then parent table. We have to do just a delete from parent table.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply