Non-clustered GUID primary key OR clustered int primary key and GUID secondary key with unique index?

Question

Pros/cons with these alternatives?

1. Having a non-sequential GUID primary key with a non-clustered index
2. Having a sequential integer primary key with a clustered index and the random GUID as a secondary key with a unique index

I will be fetching objects on the GUID-key but I'm wondering if there is any benefit of having a sequential primary key with clustered index for any other reasons?

I could of course use a sequential GUID and then have both GUIDs and a clustered index, but putting that option aside, what alternative is better?

Answer 1

The advantage are joins. I use that approach you have very often - a non-clusterd unique GUID as identifier of the object, and an int / bigint it field as primary key that is then also used for joins.

Answer 2

If you create additional indexes on the table, those indexes will reference the table data by their Primary Key values. So if you keep your PK columns small, you will have an advantage. My answer would be to create a small int (IDENTITY) Primary Key, clustered, and have a Unique unclustered index on the GUID values.

Answer 3

I think if you are ONLY fetching records using your GUID, then you have no need for the sequential integer primary key. However, if you are querying your table in any other way then I would suggest looking at having a surrogate integer primary key as a clustered index. Without a clustered index, your table becomes a heap and SQL Server will add a row identifier to every record, which may be larger than your surrogate key column. See here:

http://msdn.microsoft.com/en-us/library/hh213609.aspx

If possible I would also look to retrieve records using the surrogate key rather than the GUID.