Fairly sure a partitioned SQL Server table can be referenced in a foreign key. Even if it can't the FK is there logically anyway.
The real reason why relational databases don't scale well is to do with locking and contention. NoSQL often gives up consistency guarantees to allow better performance. There is also the issue that not all data is relational so other data structures are just better for some tasks, but that applies at small scale as well.
Fairly sure a partitioned SQL Server table can be referenced in a foreign key. Even if it can't the FK is there logically anyway.
The real reason why relational databases don't scale well is to do with locking and contention. NoSQL often gives up consistency guarantees to allow better performance. There is also the issue that not all data is relational so other data structures are just better for some tasks, but that applies at small scale as well.
Do you have an implementation of doing a join across partitions?
Yes
Can you share the details? I would like to see how to do joins across separate databases