GUID or not?

JonM posted on Thursday, June 22, 2006

Hey everyone, I'm looking for quick opinion of what other people are doing. I'm currently working on a medium size CSLA project. So far the project has been built using GUIDs as the primary key. The main reasoning behind this is that we may eventually want offline versions and will use merge replication. However, more and more, I seem to think that the offline version is less important. Most new smartphones can be connected over the internet, and you can even buy high-speed EVDO cards for tablets/laptops. Does it make more sense just to use integer/autonumber type primary keys and forget about offline modes? I guess I'm worried that in the long-term guid keys will give me database slowdown (sql server 2005) over a integer key. Any suggestions on this? I'm still at a point where I can convert, however once data is live it will be nearly impossible to switch.

Bill replied on Thursday, June 22, 2006

Merge replication requires the presence of ROWGUIDCOL, but it doesn't have to be the primary key.

Brian Criswell replied on Thursday, June 22, 2006

Guids also have the advantage of not having to be retrieved after an insert and being unique for shared data across applications. I have an application two applications synchronising data with integer PKs and I really wish that the original architect had gone with Guids.

BMStroh replied on Thursday, June 22, 2006

I've seen an article somewhere that proposed something called a COMB - it was GUID-ish, but was much more index-friendly to the database, because it wasn't entirely random.

I'm not an expert in this area by a long shot, so I've always been curious to learn if the idea was as sensible as it seemed at the time when I was reading it.

brettswift replied on Thursday, June 22, 2006

I noticed one small perk to having a GUID. I created a TreeView control which has objects and subsets of objects etc etc. I don't have to get the type of each one in the looping and compare it to each of my BO types to figure out which I'm dealing with - or use reflection or something along those lines, I simply just compare it to the GUID I'm looking for to find the right treenode that I want to deal with...

A specific example, but should give you an idea of a GUIDS versatility.

guyroch replied on Friday, June 23, 2006

Read the following thread... http://www.searchcsla.com/Details.aspx?msn_id=22852

and read this article too... http://www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1

JonM replied on Sunday, June 25, 2006

Thanks for the responses everyone. I really wanted to make sure I wasn't doing something that was going to bite me in the near future. This application may be up and running for a long 5-10 years. (Applications always seem to like twice as long as you think). Also, it would be a nightmare to convert the system from guid to integer (or vise versa) once the system is online with production data that uses those primary keys for relation. With the good possibility of a mobile extension to the application coming the next 1-2 years, already having a guid key will keep from having to add columns (and increasing the row size). It looks like I might even be able to use that new sql anywhere microsoft keeps talking about. As anyone figured this new database edition out? It sounds like mobile edition to me (the version for tablet and pocket pcs).

odie replied on Monday, June 26, 2006

Here is the COMB guid function I converted from an article I read on performance issues using full guids and indexing in a database. If I converted it correctly that is? It is functioning OK in the programs I have developed over the past year or two.

This function takes the first portion of a standard guid and combines it with the byte values of a portion of the Today.ToFileTimeUtc function.

I will have to see if I can dig up the article as a reference to read...

Additionally, with this function part of the guid remains constant for each day you generate them, and though it does slightly increase the possiblity of a duplicate guid being generated it is still fairly remote.

Here is a sample of guids generated:
2d92f21a-f13f-4afb-9fc1-37c2e598c601
a9e2a31d-df4d-45a8-a16f-37c2e598c601
0b24591b-77d6-47f9-94dc-37c2e598c601
a7d99d1b-3270-4f78-b118-37c2e598c601
455d5db7-f3a7-443f-b5da-37c2e598c601

Public Class Guids

''' <summary>

''' Custom guid: Returns a mofidied guid paritially based on current date

''' </summary>

''' <returns>Custom Guid</returns>

''' <remarks>Using this guid reduces database seek times with large number of records compared to standard guids</remarks>

Public Shared Function CustomGuid() As Guid

'Function to generate custom GUID's

Dim nGuid As Guid

nGuid = Guid.NewGuid

Dim DateByteArray As Byte()

DateByteArray = System.BitConverter.GetBytes(Today.ToFileTimeUtc)

'need to get the first 10 bytes of nGuid and the last 6 of DateByteArray

Dim GuidByte As Byte() = nGuid.ToByteArray

Dim index As Integer

For index = 0 To 5

GuidByte(10 + index) = DateByteArray(index + 2)

nGuid = New Guid(GuidByte)

Return nGuid

End Function

End Class