I just came across this. Let us say I implement marc_s's suggestion:
CREATE TABLE dbo.MyTable (PKGUID UNIQUEIDENTIFIER NOT NULL, MyINT INT IDENTITY(1,1) NOT NULL, .... add more columns as needed ...... ) ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (PKGUID) CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable(MyINT) Would I therefore always use MyINT as foreign key in any joined tables?
PS:
I think this answers it but I am not hundred percent sure.
Using the clustered index as foreign key should speed up joins.
But you can as well index your GUID column and use it for the join, although expect a little worse performance.
You must think as well if the GUID provides you extra information (maybe you aren't using random GUIDS but email addresses?), in that case it could have other benefits to use them as foreign keys instead of random numbers. E.g. in certain queries you may not need to even join to the main table to do your filters as you may now the GUID beforehand.
Yes, if you specify MyInt as the referencing column in the Foreign Key Constraint or a Join-Condition. That's because MyINT is the Key of an index and SQL-Server can seek directly.
