asked
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
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.
3 People are following this question.
