Jeffrey Palermo 12:09 am on August 20, 2006
Tags: Data Access ( 6 ), Tips & Tricks ( 43 )

Use guid.comb in your database if you need guid keys but don’t want to take a big performance hit – level 300

See http://jeffreypalermo.com/blog/use-guid-comb-in-your-database-if-you-need-guid-keys-but-don-t-want-to-take-a-big-performance-hit/ for this article.

Jon Galloway 1:48 am on August 20, 2006

Another option (in Sql Server 2005) is NEWSEQUENTIALID(), which generates a GUID which is greater than any previous GUID on that computer. If you set NEWSEQUENTIALID() as the default on a PK GUID column, you’ll avoid the fragmentation problem.

http://msdn2.microsoft.com/en-us/library/ms189786.aspx
Melvin Lee 11:11 am on August 20, 2006

Hi Jeffrey,

Can you tell me for what situations have you used (comb) guids? I had a situation at a customer where the customer have several applications that share part of the database schema, but not the database instance. One application will aggregate through all the database instances and display the data as it is from one database. My solution was to use guids so that I don’t have to assign ranges to applications. This is the only situation I can think of why I should use guids.
C-J Berg 3:12 pm on August 20, 2006

Melvin Lee: Yes, you would want to use it to ease replication scenarios.

As Jon wrote, there’s a new function in SQL Server 2005 that does exactly this, NewSequentialID. Just be sure to read the following notes on the subject:

http://blogs.msdn.com/sqlprogrammability/archive/2006/03/23/559061.aspx
Melvin Lee 7:30 pm on August 21, 2006

C-J,

I understand why you should use comb guids. But what I’m actually looking for are scenarios where I should use guids instead of plain integers for surrogate keys. Other than the scenario I have described I can’t think of another situation where I would use guids as keys
ScottBellware 4:15 am on August 22, 2006

I typically prefer GUIDs for ID’s in place of integers if the integer ID’s have to be generated by a database server.

I’ve never really been comfortable with the idea that an abstraction like a business object – something that can be used for more than persistence to the application’s database – must get it’s ID from an external, and very likely, distributed system. Since a GUID’s uniqueness is ensured algorithmically, and since it can be done in the same local memory space as the object that it identifies, it seems a bit off to me that we’d defer to a remote dependency for something that is easily had locally, and that can be kept close to the object that is its primary client, and that is most concerned with it.

Since SQL Server 2005 has addressed the index fragmentation issue with GUID-based indexes, and since the COMB GUID generation code is easily had, GUIDs are a much easier decision to make.

That said, there are still compelling reasons to use integer ID’s, but the vast majority of database apps aren’t of the ilk that require integer ID’s specifically.

Comments are closed.

Programming with Palermo

Jeffrey Palermo, Microsoft MVP, Author, Speaker, Clear Measure Chief Architect, Azure DevOps Expert

Use guid.comb in your database if you need guid keys but don’t want to take a big performance hit – level 300

Related