By maintenance do you mean drop and recreate the table as we did?

Or is there another way?

Thanks for the reply, how can this space be recovered?

Looking into this further, it appears to be an issue with the ORDER of the columns which constitute the primary key.

Thus the following:

ALTER TABLE Y

ADD CONSTRAINT [FK1]

FOREIGN KEY(LineCreatedAt,LineId)

REFERENCES X(CreatedAt,...

Interesting, thanks for saving me the trouble of doing that - although could it be the fact that the partitions are on different filegroups in my case?

The code i'm using...

returns:

PK_Line_Partitionedclustered, unique, primary key located on PS_LineCreatedAt CreatedAt, LineId

I am beginning to think it is because these columns are on a partition scheme (PS_LineCreatedAt)

whereas the same columns on the target...

Hi,

They are definitely the only columns constituting the primary key.

I've checked this several times - only these 2 columns have the yellow key icon and "PK" next to them.