When a detail table contains denormalized data, should denormalized columns be included in foreign key relationships between the master table and detail table? Here's more details:

We have a master/detail pair of fact tables: an Invoice table with about 1M rows and an InvoiceDetail table with about 20M rows. To improve reporting performance for date-range queries we've partially denormalized by adding SaleDate to the InvoiceDetail and creating a covering index on SaleDate with the other columns INCLUDEd.

There's already a foreign key relationship between the InvoiceId column in both tables.

But SQL Server is unaware that the SaleDate in both tables is the same if the InvoiceId is the same. Should I help SQL Server to know about the relationship? If so, how?

Finally, will adding denormalized columns to foreign keys improve cardinality estimates when joining the master/detail pages by telling SQL Server that cardinality shouldn't be reduced when filtering both tables by the same a date range? If not, then what's the benefit of maintaining this foreign key relationship that includes the denormalized column?

We're running SQL Server 2014 an are soon upgrading to SQL 2017, if that matters to the answer.

| improve this question | | | | |
  • You mention fact table, is this a dedicated reporting data warehouse? Or are you trying to improve reporting performance over an OLTP database? – Mark Storey-Smith Feb 17 '18 at 16:28
  • @MarkStorey-Smith - It's an OLTP database, but these two tables are optimized for read-only reporting and are updated by an out-of-band process rather than being involved in each transaction. So from the perspective of reporting on these two tables, it's more like a data warehouse use-case than OLTP. – Justin Grant Feb 17 '18 at 17:11
  • Can you share schema and an example problem query, execution plan, server specs? What's the response time for typical queries currently and what are the hopes/expectations for "good"? – Mark Storey-Smith Feb 18 '18 at 14:02
  • Maybe you want to hear it from someone else, but as I said last time foreign keys unfortunately do not improve cardinality estimates. How large is the Invoice table in KB? – Joe Obbish Feb 18 '18 at 20:13
  • 1
    Sorry @JoeObbish, was posting this on behalf of a co-worker who was not familiar with your godlike skills when it comes to the dark arts of cardinality estimation troubleshooting. ;-) As you might guess from the wording of this question, I was dubious that foreign-key extension was going to be useful for cardinality, but was wondering if might be useful for other reasons. The Invoice table is ~85MB. InvoiceDetail table is ~1.3GB. Also, I couldn't find that Connect item you mentioned in your other answer. Was it lost in UserVoice migration? feedback.azure.com/forums/908035-sql-server – Justin Grant Feb 21 '18 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.