We use Guids as primary keys for entities in the database. Traditionally, we've followed a pattern of letting the database set the ID for an entity during the INSERT, I think mostly because this is typically how you'd handle things using an auto-increment field or whatever. I'm finding more and more that it's a lot handier to do key assignment in code during object construction, for two main reasons: you know that once an object's constructor has run, all of it's fields have been initialized. You never have "half-baked" objects kicking around. if you need to do a batch of operations, some of which depend on knowing an object's key, you can do them all at once without round-tripping to the database. Are there any compelling reasons not to do things this way? That is, when using Guids as keys, is there a good reason to leave key assignment up to the database? Edit: A lot of people have strong opinions on whether or not Guids should be used for PKs (which I knew), but that wasn't really the point of my question. Aside from the clustering issue (which doesn't seem to be a problem if you set your indexes up properly), I haven't seen a compelling reason to avoid creating keys in the application layer.

Reputation: 2880

Difference between creating Guid keys in C# vs. the DB

We use Guids as primary keys for entities in the database. Traditionally, we've followed a pattern of letting the database set the ID for an entity during the INSERT, I think mostly because this is typically how you'd handle things using an auto-increment field or whatever.

I'm finding more and more that it's a lot handier to do key assignment in code during object construction, for two main reasons:

you know that once an object's constructor has run, all of it's fields have been initialized. You never have "half-baked" objects kicking around.
if you need to do a batch of operations, some of which depend on knowing an object's key, you can do them all at once without round-tripping to the database.

Are there any compelling reasons not to do things this way? That is, when using Guids as keys, is there a good reason to leave key assignment up to the database?

Edit: A lot of people have strong opinions on whether or not Guids should be used for PKs (which I knew), but that wasn't really the point of my question.

Aside from the clustering issue (which doesn't seem to be a problem if you set your indexes up properly), I haven't seen a compelling reason to avoid creating keys in the application layer.

Upvotes: 13

Answers (9)

marc_s

Reputation: 754488

Aside from the clustering issue (which doesn't seem to be a problem if you set your indexes up properly),

GUID as indexes will always be terribly cluttered - there's no "proper" setup to avoid that (unless you use the NEWSEQUENTIALGUID function in the SQL Server engine).

The biggest drawback IMHO is size - a GUID is 16 byte, an INT is 4. The PK is not only stored in the tree of the primary key, but also ON EVERY non-clustered index entry.

With a few thousand entries, that might not make a big difference - but if you have a table with millions or billions of entries and several non-clustered indices, using a 16-byte GUID vs. a 4-byte INT as PK might make a HUGE difference in space needed - on disk and in RAM.

Marc

Upvotes: 0

Randolpho

Reputation: 56391

Ok, time to chime in. I would say that generated GUIDs client-side for saving to the database is the best way to do things -- provided you happen to be using GUIDs as your PKs, which I only recommend in one scenario: disconnected environment.

When you are using a disconnected model for your data propagation (i.e. PDA/cellphone apps, laptop apps intended for limited connectivity scenarios, etc), GUIDs as PKs generated client-side are the best way to do it.

For every other scenario, you're probably better off with auto-increment identity PKs.

Why? Well, a couple reasons. First, you really do get a big performance boost by using a row-spanning clustered PK index. A GUID PK and a clustered index do not play well together -- even with NEWSEQUENTIALID, which, by the way, I think totally misses the point of GUIDs. Second, unless your situation forces you not to (i.e. you have to use a disconnected model) you really want to keep everything transactional and insert as much interrelated data together at the same time.

Upvotes: 0

Joe Doyle

Reputation: 6383

As SQLMenace noted, standard GUIDs negatively affects indexing & paging. In C# you can generate sequential GUIDs like NEWSEQUENTIALID() using a little P/Invoke fun.

[DllImport("rpcrt4.dll", SetLastError = true)]
static extern int UuidCreateSequential(out Guid guid);

This way you can at least keep using GUIDs, but get more flexibility with how and where they are generated.

Upvotes: 0

MBoy

Reputation: 704

Interesting question.

Traditionally I too used the DB assigned guid but recently I was working on a Windows Mobile application and the SQL CE database doesn't allow for newguid so I had to do it in code.

I use SQL replication to get the data from the mobile devices to the server. Over the last 6 months I have had 40 SQL CE clients synchronise back over 100000 records to a SQL 2005 server without one missed or duplicated guid.

The additional coding required was negligible and the benefit of knowing the guid before inserting has in fact cut down on some of the complexity.

I haven't done any performance checking so performance aside I cannot see any reason not to implement guid handling as you suggest.

Upvotes: 2

HLGEM

Reputation: 96572

If you ever have to do an insert outside of the GUI (think import from another vendor or data from a company you bought and have to merge with your data), then the GUID would not automatically be assigned. It's not an insurmountable issue, but it is something to consider nonetheless.

Upvotes: 1

ChrisW

Reputation: 56113

I let an empty Guid be an indicator that this object, although constructed, has not yet been inserted into (or retrieved from) the database.

Upvotes: 0

SQLMenace

Reputation: 135011

GUIDs are horrible for performance

I would leave it in the database especially now that SQL Server has NEWSEQUENTIALID() which doesn't cause page splits on inserts anymore because the values are random, every NEWSEQUENTIALID created will be greater than the previous one...only caviat is that it can only be used as a default value

Upvotes: 1

Ricardo Villamil

Reputation: 5107

By doing it in C# you might run the risk of reassigning the GUID and saving it back to the database. By having the database be responsible for it, you're guaranteed that this PK will not change, that is, if you set up the proper constraints. Having said that, you could set similar constraints in your C# code that prevent changing a unique id once it has been assigned, but you'd have to do the same in all of your applications...In my opinion, having it in C# sounds like is more maintenance than the database, since databases already have built in methods to prevent changing primary keys.

Upvotes: 4

Otávio Décio

Reputation: 74270

I think you are doing just fine by creating them on the client side. As you mentioned, if you let the db do it, you have to find some way (can't think of any really) to get that key. If you were using an identity, there are calls you can use to get the latest one created for a table, but I'm not sure if such exists for a guid.

Upvotes: 5

Difference between creating Guid keys in C# vs. the DB

Answers (9)

Related Questions