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

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.