I agree with Charlieface in regards to being able to use the same Primary Key field in both tables, since this is a one-to-one relationship.
But I would also like to mention, if you had a need to create a separate Primary Key field for your
Profile table, and you used a secondary field as the Foreign Key reference to your
Person table, that doesn't necessarily mean it'll "scan the whole table to get a matching" when you
JOIN by that Foreign Key field. It could (and probably would) use an index seek operation actually, and be just as performant (so long as the secondary field has a nonclustered index on it.).
This is because SQL Server doesn't care if you're using the clustered indexed fields or a nonclustered indexed set of fields to
JOIN by. Rather it determines which type of operation to use based on the cardinality of the values for the fields in your
ON clause. In this context cardinality refers to the number of rows your predicate returns relative to the total number of rows in their respective tables. The cardinality will be the same in your case whether you use the Primary Key field of your
Profile table as the same field as your
Person table or if you used a second field as such instead.