Many of my reports are essentially pivot tables. I take a set of big tables and transform each table individually so that a particular column (let's call it
CaseID) that wasn't a primary key in the original tables will be a primary key in the output (e.g. use
ROW_NUMBER() OVER([...]) and filter for where that equals 1). Let's call each output a "subtable". I then join these subtables together on the primary key to make my final output.
Intuitively, telling the optimiser that
CaseID is a primary key of each subtable (e.g. by storing the subtable in a temp table that is explicitly defined as having
CaseID as a primary key) should give a massive performance boosts and promote the usage of merge joins. In practice, I only see this benefit when the subtables are temp tables. When I do the same with a table variable, the performance that I get is no better than saying nothing about keys and just making the subtables CTEs.
OPTION(RECOMPILE) makes no difference. My temp tables are not memory optimised.
I am aware that table variables do not hold statistics, but it is as if giving a table variable a clustered primary key does nothing for performance, despite it being awesome for temp tables. Why is this? Do I have a misconception about what "does not hold statistics" means?
I have seen this exact problem in multiple contexts, so I have not given any code example. It makes little sense to give a practical example when my problem has occurred so many times that it is clear that I am ignorant of a matter of theory.