UNIQUEIDENTIFIER primary key nonclustered

Question

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.

score 1 · Accepted Answer · answered Jul 17 '15 at 10:58

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.

But be aware that an indexed GUID-column (unless you use squential IDs) leads to extrem index fragmentation... So the "performance push" will be very low... I'd use GUID only for very special needs, such as dislocated data inserts, replication... — Shnugo, Jul 17 '15 at 11:34

score 1 · Answer 2 · answered Jul 17 '15 at 11:03

1

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.

answered Jul 17 '15 at 11:03

CPMunich

725
4
13

UNIQUEIDENTIFIER primary key nonclustered

2 Answers2