Foreign Keys for Documentation Purposes RRS feed

  • Question

  • Hello,

    Our conplete DW is filled by SSIS and some SP.

    They all do their job fine and for speed purposes we didn't define foreign keys. But now we are a bit forced to use a Documentation tool. But of course as no keys are defined quite some interesting stuff is not displayed.

    I thought that defining FK's and not enforcing them would help the tool, and it does, but now the question is does this affect any speed ?

    Tuesday, May 3, 2016 7:07 PM


  • Hello,

    Defining the FKs but not enforcing them will help you save on storage and keep low the maintenance costs of the fact table. Additionally, defining the FKs will help SQL Server detect star schema query patterns, so SQL Server does not have to use heuristics.

    Hope this helps.


    Alberto Morillo

    • Marked as answer by Kinepolis Tuesday, May 3, 2016 8:13 PM
    Tuesday, May 3, 2016 7:38 PM