Assuming that you have the primary key on a clustered index then the partitioning key needs to be part of the primary key.
You will not loose the benefit of partitioning by joining to non-partitioned tables, providing that the queries are designed to make use of the partitioned table, for example the following query WILL benefit from partitioning
SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE F.Col1 = 5
But the following query WILL NOT benefit from partition elimination
SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE D.Col1 = 5
It is a subtle difference, but in the first query, the join key is filtered in the partitioned table, taking advantage of elimination and then joined to the dimension. In the second query, the key is filtered in the dimension and then joined against the whole of the fact table, rather than just required partitions.
It goes without saying that the partitioning key needs to be in the WHERE clause for elimination to work, otherwise SQL Server does not know which partition(s) the data is in.
Adding a filter criteria on the JOIN clause will not help you. It needs to be in the WHERE clause to benefit from elimination.
The Partition Key does not need to be part of a non-clustered index (NCI) but if the NCI is unique, then it needs to contain the partitioning key in order to align the index. This is where the NCI is built on the same partition scheme as the table. NCIs should also be partition aligned unless there is an exceedingly good reason not to. I have never come across a good enough reason!
If your vad_vaf_id does not sufficiently designate the target value, perhaps you should add a new column in your temporary table to provide that information? Something like vad_target?
+-----------------+ +-------------------+
| validation_data | | validation_fields |
+-----------------+ +-------------------+
| vad_id | | vaf_id |
| vad_met_id | | vaf_field |
| vad_vaf_id | +-------------------+
| vad_target |
| vad_value |
| vad_is_treated |
+-----------------+
When user changes his firstname, leave the field NULL. When there are multiple possible targets (i.e. with inverters), use inverter's id.
Best Answer
A foreign key can reference a unique constraint, not necessarily only a primary key, so if you want to be a purist, include
FinishedAt(and whatever else you need) in the parent table's unique constraint and use that to define the foreign key.