Guids vs Auto Incremented ints

Question

I am wondering if there is a best coding practice in regard to dealing with IDs for parent > child objects in the code, where the DB records use an auto incremented int as the ID (on the initial save). Of course when in the code you are unable to guess what this ID might be and therefore must leave it blank and presumably save all these items in a transaction grabbing the parent ID first and then setting it on all children before saving them

Guids on the other hand are much easier to deal with in the code, as of course you can happily generate the Id first and set it on everything and save without worry..

is there a nice easy way to deal with objects in code using auto-ints as their db keys?

thanks

Artery

Answer 1

GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.

You really need to keep two issues apart:

1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.

As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.

Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.

Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

Quick calculation - using INT vs. GUID as Primary and Clustering Key:

• Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
• 6 nonclustered indexes (22.89 MB vs. 91.55 MB)

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

• GUIDs as PRIMARY KEY and/or clustered key
• The clustered index debate continues
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!

Also: from a C# / .NET point of view - it depends on how you access your SQL Server database. If you use something like Linq-to-SQL or Entity Framework v4, your .NET objects will be automagically updated with the inserted IDs (from your INT IDENTITY column) - without you having to do anything at all. So to me, that's one less reason why you should feel the need to use GUID's....

GUID's are horribly bad as SQL Server clustering keys - not just bad - really, truly, AWFUL

Vibraharp

Very helpful for those lacking in depth knowledge of these matters.. such as myself :-) – Livable

I had already fallen foul of my predecessor using the Guid as the clustering key and wont make that mistake myself :) am not using linq-to-sql or entity framework.. maybe that is worth a look. as these tables aren't really going to be getting a bashing as such maybe Guids are the way forward, was just trying to steer clear of them unless I really had to.. hence asking if there was a cunning way of using the ints.. many thanks for your helpful answer and time.. wonder if someone could answer this a bit more from the .net side..? – Artery

+1, I was about to explain why sequential ID's are important in indexing performance. This is a good explanation. – Mcdougall

-1 from me, for missing the point of the Tripp articles. Sequential GUIDs are just fine for primary keys in almost all cases. – Vadnee

@JoeStrommen: even "pseudo-sequential" GUID's are still four times larger than an INT and thus bloat your tables (if you have lots of rows, and several NC indices). Also: most of the time, programmers pick GUID's since those can be generated on the client before inserting into the database - and those are most definitely NOT sequential! – Vibraharp

Answer 2

There are a couple of "coding" advantages to GUIDs over auto incs.

First it's decoupled, you don't need to get to the database to get an almost certainly unique id.

So you can create a new record in memory, have it now know and the pass the actual storage to some service, and then happily use it to say add related data locally and then pass that to the same or another other service. The corrolary which things like EF sort out for you under the covers is dealing with having to insert the record in the db and then get the identity teh DBMS assigned to it to pass to down stream functionality. You can avoid this if you have another unique key and the auto inc is a surrogate, but that's not a free lunch any way.

If I wasn't doing distributed and my app had to be connected to the database, and the auto inc is a true surrogate (not exposed as order number or some such numptiness), and int covered the range, and there was no possiblity of say two or my customers merging and wanting to merge their databases, then I wouldn't bother with a guid.

Coping with having one being another question as far as I'm concerned. There are issues, but they are trivial compared to dealing with your unique key not being unique.

Madeira