Identity as Primary Key, UniqueIdentifier as Foreign Key

I’m building several web based applications in C# and using SQL Server as the database server and using Entity Framework 4 as the ORM. Periodically we merge data between various data sources so we currently use uniqueidentifiers as primary and foreign keys. However since it’s the primary key and clustered when we merge data from various (like) sources we severely jack with the clustered index. I’m currently developing a new data schema and trying to avoid the situation described above I was thinking of using identity (int) columns as the primary key and using uniqueidentifiers as a uniquely indexed column and use that column as the foreign key. Does that make any sense?

Primary Table CREATE TABLE [dbo].[Detail] [DetailId] [int] IDENTITY(1,1) NOT NULL, [DetailUid] [uniqueidentifier] NOT NULL this is a unquiely indexed column And so on

Child Table CREATE TABLE [dbo].[DetailItems] [DetailItems Id] [int] IDENTITY(1,1) NOT NULL, [DetailUid] [uniqueidentifier] NOT NULL - this is the foreign key assocated with the [DetailUid] column from the Detail table And so on

more ▼

asked Feb 24, 2012 at 10:30 PM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The main problem with GUIDs is when they're the clustered index. Because of their basically random nature, it leads to serious fragmentation, as you're seeing. Your solution, focused on the primary key, is actually a solution to fix the clustered index. You're still going to get a very fragmented index on the GUID and you're going to be adding a key lookup operation to most of your queries (assuming the GUID is the primary path to the data).

Yes, what your proposing is going to help the cluster, some. But since you're still mostly using the GUID anyway, I think your introducing issues elsewhere. Fragmentation is a bear, but what about using index compression and a lower default fill factor on the indexes as ways to help manage the fragmentation? Then you don't have to mess with the design in odd ways and your most direct path to the data and the most frequently used path are one and the same.

Since you can't eliminate the GUIDs, I'd take the approach of trying to deal with them rather than try to structure around them that way.

more ▼

answered Feb 25, 2012 at 11:46 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 24, 2012 at 10:30 PM

Seen: 2597 times

Last Updated: Feb 25, 2012 at 11:46 AM

Copyright 2018 Redgate Software. Privacy Policy