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.