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