x

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 '12 at 10:30 PM in Default

croberts gravatar image

croberts
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 '12 at 11:46 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x34
x33
x32

asked: Feb 24 '12 at 10:30 PM

Seen: 1192 times

Last Updated: Feb 25 '12 at 11:46 AM