Currently I'm working in an environment where nothing - absolutely nothing in our database is relational besides what we define being relational through stored procedures and other functions.
I have no idea currently what would happen to our system if we decided to actually connect everything through proper constraining, but what I'm asking is this:
Would there be any performance increases, in implementing proper constraining?
Primary keys and foreign keys are not performance boosters, they are not designed for performance and the reason to use them has nothing to do with performance.
They exist for a much more important reason - to make sure your data is valid.
This is called data integrity, and it's one of the main reasons why relational databases are used on the first place.
Primary keys are implemented as a unique index on a non-nullable column (or a group of non-nullable columns) which means they might help with query performance (as with all indexes, they can boost select performance and somewhat degrade insert/update/delete performance) - but that is not what they are used for - it's merely a side-effect caused by how they are implemented by the database.
So, to answer your question - You might expect some performance gain in select statements when adding primary keys, but much, much more important - you gain data integrity by adding keys (both primary and foreign) to your database.
Would there be any performance increases, in implementing proper constraining?
Yes, there likely will be.
Imagine you want to insert a "child" row which cannot exist without the corresponding "parent" row. How would you check for it in a stored procedure?
Well, naively, you would:
IF EXISTS (SELECT * FROM PARENT WHERE ...)
INSERT INTO CHILD VALUES (...);
ELSE
THROW ...;
But of course, a concurrent transaction might delete the parent row between IF and INSERT, so you'd need to lock the parent row:
...SELECT * FROM PARENT WITH(UPDLOCK)...
But now that lock is held to the end of the transaction, blocking anyone wishing to modify parent. Or SQL Server might decide to escalate to table lock, at which point your concurrency goes down the drain...
Letting the database enforce FKs will likely allow for more concurrency and better performance.
A SELECT query can also benefit from declarative foreign keys.
If you have an INNER JOIN, but select columns from only the child table, the query optimizer may skip accessing the parent table completely - it already knows that if the child row exists the parent row must exist too, so it doesn't need to check for parent existence explicitly.
Simply omitting the parent from the query is easy enough if you have just one query, but may not be very practical if you have layers of views and inline table-valued functions. In that case you'd like to reuse the existing code without having to modify it just to cull the "extra" processing that you don't need, so you can let the query optimizer cull it for you.
